SISS: Providing parameters when providers don't support them

  • Article

This has come up a few times recently, and I couldn't find the information for them in the usual sources.

The desire that some folks have is to support parameters in their SQL statements. Sounds reasonable. The OLEDB source, for example, supports parameters. However, some providers don't support derivation of parameters from a statement. The question is how to make parameterized queries work for those providers.

For the OLEDB Source, the trick is to use the 'SQL Command from variable' access mode. What that means is that the sql command used during runtime will come from the contents of a variable. As you may know, in addition to just storing values, Variables can also store expressions which are evaluated to return the variable's value.

Lets work through an example. Lets say you want to execute this statement:

      SELECT Income from Customer where Age > ?

But your provider doesn't support finding out there's one parameter in this statement.

What we can do here is to create two variables. Below we have the properties you'd set for the two variables displayed in a '<prop>:<value>' format.

#1:

   Name: AgeVariable

   Type: String

   Value: 10

#2:

   Name: SqlStatementVariable

   Type: String

   Expression: "SELECT Income from Customer where Age > " + @AgeVariable

   EvaluateAsExpression: True

In this case I've hard coded the variable value to 10, but of course you can use whatever mechanism you want to (result of an Execute SQL task, the current value of a loop enumerator, etc.) to set that variable value.

Now we can use the 'SqlStatementVariable' as the source for the OLEDB Source.

How about on the destination side? What if I'm inserting into the customer table and want to set Age to 10, lets say, for all the rows.

The trick there is to introduce the variables as columns inside your data flow using something like a Derived Column transform and then map this column to the colunms of your table.

Hope this helps. If not, let me know and I can edit this post to make it more useful.

regards,

ash