SSIS: Passing Parameter in OLE DB Source Editor to Table Function
How to pass parameters in OLE DB source to call a table function like:
select * from [dbo]. [udf_test] (?,?)
When doing this, I get the error:
Parameters cannot be retrieved from SQL command. The provider may not help you parse the parameter data from the command. In this case, use the SQL command form access mode, in which the entire SQL command is stored in a variable. Syntax error, access violation, or other non-specific error (Microsoft SQL Native Client)
Is there any other solution than "SQL command from variable"?
Sven
a source to share
You can use SQL command from variable, it is relatively simple. You can create an SQL statement to navigate to Source using a Script Transformation to edit a variable that contains SQL. So for example you set up a strSQL variable and fill in the value
select * from [dbo].[udf_test](?1, ?2)
And then in the Script component, replace the values? 1 and? 2 parameters you want to pass using string manipulation methods.
This works a little more in a package, but works well in my experience.
a source to share
This is a bit of a workaround, but it works very well. We create a SetVar stored procedure that will take an input parameter and assign it to the declared parameter.
Create a procedure called SetVar in the source OLE DB database. It assigns @inputVar @outVar
CREATE PROC SetVarInt
@inputVar INT ,
@outVar INT OUTPUT
AS
BEGIN
SELECT @outVar = @inputVar;
END
In SSIS OLE DB source, use this SQL:
DECLARE @param1 INT;
EXEC [dbo].[SetVarInt] @inputVar = ?, -- int
@outVar = @param1 OUTPUT -- int
SELECT * FROM [dbo].[GetByKey](@param1)
If nothing happens, the optimizer can optimize the task (check the package execution log). In this case, I will add the following to the end of the original OLE DB source and connect that output to the RowCount task in a dummy variable.
SELECT 1 Done;
a source to share