April 7, 2008 at 1:31 pm
1. I have read in blogs that it is possible to call table valued functions in SSIS just as you would use a table or a view. My tvf has a parameter. When I enter the statement in the SQL command text box of the OLE DB Source Editor:
SELECT Col1, Col2, Col3
FROM tvfTable(?)
I get an error message if I click ok:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source 1 [2932]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
And this one if I try to define the parameter:
TITLE: Microsoft Visual Studio
------------------------------
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.
------------------------------
ADDITIONAL INFORMATION:
Syntax error, permission violation, or other nonspecific error (Microsoft SQL Native Client)
------------------------------
BUTTONS:
OK
------------------------------
and I am not given the opportunity to define the parameter. Is there a trick to this, and if so, what is it? I have been working around it by creating a stored procedure which calls the tvf, which works, but seems like a waste.
2. I (not the dba) created a stored procedure in SQL Server 2005. This stored procedure contains a common table expression that pulls its data from a tvf. One column in the tvf contains money data which is added to the data set as a negative if a scalar function returns 0. (Both the tvf and the common table expression use scalar functions in creating the data set. ) When I execute the stored procedure, I have both positive and negative values. When the SSIS package executes the stored procedure, it incorrectly produces output that contains only positive values (all values are returned as positive, even if they should be negative.) I asked my colleague to run the stored procedure and she is getting only positive values in the data. The dba gets both negative and positive values. I figured this was a permissions thing and set the permissions on all the functions (scalar and tvf) to "select" for all users who have access to the application and I set the permissions on the stored procedure to execute for the same users. Still, all amounts are being returned as positive for everyone but the dba and me. Here's a simplified example of the stored procedure and table valued function:
Table Valued Function:
FUNCTION [dbo].[tvfTable]
(
@ExportDateString varchar(10)
)
RETURNS @tvfTable TABLE
(
Col1 varchar(30),
Col2 money,
Col3 varchar(10)
)
AS
BEGIN
INSERT @tvfTable
SELECT Col1 = COALESCE(oc.vendor_id,'0'),
Col2 =
CASE
WHEN oc.void = 1
AND cashier.dbo.fVoidRequestDate(oc.void_dtt, @ExportDateString) = 1
THEN -od.amount
ELSE od.amount
END,
Col3 = oc.oblig_id
FROM databaseA.dbo.Table1 oc
JOIN databaseA.dbo.Table2 od
ON oc.oblig_id = od.oblig_id
WHERE (oc.trx_dtt BETWEEN databaseA.dbo.fEarliestDatetime(@ExportDate)
AND databaseA.dbo.fLatestDatetime(@ExportDate)
AND (oc.void = 0
OR (oc.void = 1
AND (oc.trx_dtt <
databaseA.dbo.fEarliestDatetime(oc.void_dtt)
OR oc.trx_dtt >
databaseA.dbo.fLatestDatetime(oc.void_dtt)))))
OR(oc.void_dtt BETWEEN databaseA.dbo.fEarliestDatetime(@ExportDate)
AND databaseA.dbo.fLatestDatetime(@ExportDate)
AND (oc.trx_dtt < databaseA.dbo.fEarliestDatetime(@ExportDate)
OR oc.trx_dtt> databaseA.dbo.fLatestDatetime(@ExportDate)))
RETURN
END
Stored Procedure:
WITH ObligationRecord
(SELECT Col1, Col2, Col3
FROM tfvTable)
SELECT Col1, Col2, Col3
FROM ObligationRecord
ORDER BY Col2, Col1
It appears that the scalar function checking the void date is not working for anyone but the dba and me, although if my colleague runs the tvf only, she gets both positive and negative values. When she executes the stored procedure, she gets the unsigned, absolute values.
Please let me know how to resolve this so the proper values will be returned by the SSIS package.
Thanks,
April 8, 2008 at 4:20 am
I'll look at the proc in a moment, but in regards to getting your table valued function to run in SSIS, what T-SQL do you use to get it to run in Management Studio? Is it running properly in SSMS, but not running properly in SSIS?
Let us know.
April 8, 2008 at 4:25 am
BTW, BOL has this to say about Table Valued Functions:
Books Online:
Specifies that the return value of the table-valued function is a table. Only constants and @local_variables can be passed to table-valued functions.
Are you passing in a constant? Or a variable-set parameter?
April 8, 2008 at 5:18 am
Yes, it runs in SMS just fine: SELECT Col1, Col2
FROM tvfTable(@Value)
April 8, 2008 at 5:22 am
I have a variable in my SSIS package. When I run a procedure, I substitute the variable for the parameter: in SSIS the SQL looks like: EXECUTE databaseA.dbo.pProcedure ? and I define the parameter as equal to the variable. When I try SELECT Col1, Col2 FROM tvfTAble ? in SSIS, I get the errors listed above. It won't let me define the parameter.
April 8, 2008 at 5:28 am
Hi Ann,
There are certain statements that you can't parameterize in Execute SQL task or OLEDB source.
From memory they are sub-selects, joins and functions (there may be more). In these instances what you do is create the SQL statement from a variable. You have to build the statement dynamically by setting the property of EvaluateAsExpression to true.
Hope this helps
Kindest Regards,
Frank Bazan
April 8, 2008 at 5:32 am
Given what BOL says about functions and variables, Frank's statement makes sense to me. Try building your function SELECT as a dynamic SQL Statement.
Do you know how to do dynamic SQL?
April 8, 2008 at 5:48 am
Yes, and so that solves #1 - thank you. Anyone know about # 2, why the function wouldn't be working for anyone but the owner and the dba?
April 8, 2008 at 5:53 am
Unfortunately, since I don't have the table structure you do (or the data), I can't test this. Have you verified the Execute permissions on the Function?
And is there another function with a similar name but a different schema that could be accidently being called?
April 8, 2008 at 6:10 am
Yes, I have verified the permissions on all the objects involved -- everyone who has access to the application has Select permissions on the tvf, and Execute permission on the scalar function and the stored procedure. The code is all correct so far as object names are concerned. The data returned in SMS for the owner and the dba are correct; the data returned in SMS for others are the absolute value of the field (which means the function used to determine if the number should be a negative isn't returning the correct value). The SSIS package, which writes the data to a flat file, is writing the absolute value of the field to the flat file, regardless of who runs it.
April 8, 2008 at 6:12 am
And there's no actual physical table by the same name that might have different values than the function uses?
EDIT: And have you tried the stored procedure without a CTE? Maybe it's the CTE that's doing the conversion?
April 8, 2008 at 6:14 am
No, the names are all unique.
Thanks
April 8, 2008 at 6:28 am
Have you checked regional or date format settings on the machine that's executing it?
If the dates are being passed in different formats according the user, then you may be seeing strange results... this has happened to me before.
Kindest Regards,
Frank Bazan
April 8, 2008 at 6:29 am
I'm testing various alternatives. I just stripped the CTE and ran it, ended up with positive values in the flat file. I rewrote the code for determining the sign of the number:
CASE
WHEN oc.void = 1
AND oc.void_dtt BETWEEN databaseA.dbo.fEarliestDatetime(@ExportDate)
AND databaseA.dbo.fLatestDatetime(@ExportDate)
THEN -od.amount
ELSE od.amount
END
(Still with the CTE stripped out). I ran it and got the correct values in the flat file. All three functions (the two used above and the one replaced) have the exact same permissions and the same owner. The replaced function is being used in other code called in SSIS packages that return the correct data -- tested very thoroughly. I am really flummoxed.
April 8, 2008 at 6:31 am
Ann,
Can you post the table structures and some sample data for each table? This would help us help you test it.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply