Parameterize ADO.NET Source in Data Flow

Parameterizing OLEDB as source in Data Flow is easier. It’s not that straight using ADO.NET.

In SSIS you can’t parametrize ADO.NET source. You have to use a workaround.

Luckily, there are few workarounds. One would be creating Script Component that acts like source and code it. However, one can’t always easily convert the existing resource into script, especially when he lacks ADO.NET programming knowledge.

There is another workaround, and that would be creating the SQL Query before the ADO.NET Source takes action. However, when you open ADO.NET source, you will notice that Data access mode doesn’t allow variable input. So, how do you proceed?

You want to dynamically set the SQL expression of the ADO.NET source, so you have to tell your data flow task to configure the SSIS ADO.NET source component by using Expression.

To make the long story short (or not-quite-so-short :), do this:

• in your package, enter your data flow task with source/destination components
• click anywhere on the background, to have Task properties shown in Property panel
• in Property panel find Expressions property, that can configure various data source/destination properties, and open it using ellipsis button (…)
• under Property, select SQL Command property of your source (e.g. [ADO.NET source].[SqlCommand]) to add one row
• click ellipsis button for the row to open Expression Builder
• build your dynamic query in the Expression Builder

The last step could be somewhat cumbersome for date/datetime parameter. However, here’s the example, for your convenience:

"SELECT * FROM YOUR_SOURCE_TABLE WHERE your_date_column = '" + 
  (DT_WSTR,4)YEAR(@[User::VAR_CONTAINING_DATE]) + "-" +
  (DT_WSTR,2)MONTH(@[User::VAR_CONTAINING_DATE]) + "-" +
  (DT_WSTR,2)DAY(@[User::VAR_CONTAINING_DATE]) + "'"

Here is a reference for using OLEDB as source;

Using Variables in SSIS: OLE DB Source Parameters

Add to favorites