SSIS: Passing parameter in OLE DB Source Editor to a table-valued function

Question

How can I pass parameters in an OLE DB Source to call a table-valued function like:

select * from [dbo].[udf_test](?, ?)

When doing so, I am getting the error:

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 form variable" access mode, in which the entire SQL command is stored in a variable. Syntax error, permission violation, or other nonspecific error (Microsoft SQL Native Client)

Is there another solution than "SQL command from variable"?

Sven

Solution

You can use the SQL Command from Variable, it is relatively straightforward. You could build the SQL statement to pass to the Source using a Script transform to edit the variable which holds the SQL. So for example you'd set up a variable called strSQL and make the value

select * from [dbo].[udf_test](?1, ?2)

And then in the script component, replace the ?1 and ?2 values with the parameters you want to pass using string manipulation methods.

It's a little more work in the package but works well in my experience.