Home > SSIS Best Practices > SSIS #101 – Use expressions to pass parameters to ADO.NET Source

SSIS #101 – Use expressions to pass parameters to ADO.NET Source

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:



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.

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: