I have several posts on how to pass parameters to

1) an OLE DB source in a data flow and

2) to an Execute SQL Task with OLE DB connection.

For case 2), the key is to use the correct mark for the passed in parameter. Different connection types demand different ways to mark the parameters.

For case 1), changing the OLE DB source to an ADO.NET source in the data flow will change the game completely.

The familiar button Parameters… will disappear if the data flow source is an ADO.NET source.

You feel deflated when you don’t see the Parameters… button. But hold on.. There is actually a very elegant way in SSIS to pass variables to queries. As a matter of fact, a more generic way. That is to use expressions with the variables embedded.

To use expressions for an ADO.NET source in a data flow, here are a few simple steps:

Step 1 – Go to the Data Flow Task Properties window

On the Control Flow tab (not the Data Flow tab), right-click the Data Flow Task you are working on and select Properties.

Step 2 – Open the Property Expression Editor

In the Properties window, find a property called Expression and a small button next to it. Click the button to open the Expression Editor.

Step 3 – Open the Expression Builder for SQL Command

In the Property list, select [ADO NET Source].[SqlComamnd] and click the button next to the Expression column to open Expression Builder.

Step 4 – Write query with variables

Start to write your query with variable names and click the Evaluate Expression button to test the expression.

My simple example:

SELECT * FROM T_COMPLETE_ACCOUNT 
WHERE ACCOUNT_ID = ’" + @[User::varACCOUNTID] + "’

Save your package and test it. This is it.

You might ask why I said this is a more generic way. It is because you can use expressions with variables for not just ADO.NET data flow source, you can use expressions to work with almost any SSIS data elements. But the question is, how often do I use expressions in my queries. Not very often, I have to say. The reason is that the expressions can get out of control very quickly when my queries become more and more complex. I just simply don’t want to look at my queries and not be able to tell what it does in one glance.

Anyway, this should get your opinion about SSIS back on track. It is a wonderful tool after all.