Using a variable in an OLE-DB Source (SSIS)

In Integration Services you may want to build the SQL in your Data Flow Task dynamically by using a variable. To get this done, you need a trick.

You would expect that you can type a statement such as SELECT * FROM Sales WHERE DatumID = ? in your OLEDB Source and then link a parameter to the variable. Today I found out that this does not work. Below is a workaround.

Problem

We want to build a dynamic SQL statement in an OLEDB Source that gets the data from a table for today’s date. This date is an integer field, 20090225 (so not a datetime). We do this because of how dates are compared. When you work with datetime fields, the minutes/hours must also be equal when you compare them. With integers you do not have that problem.

Step 1

Create a variable at package level (scope) of the type Int32. Name this variable: VandaagDatumID. Click the variable and press F4 to open the properties window. Set EvaluateAsExpression to TRUE and enter the following expression below it: YEAR(getdate() ) * 10000 + Month(getdate() ) * 100 + DAY(getdate()) . This expression converts today’s date value to an integer field.

Step 2

Create another variable with the Data Flow Task as its scope. If you do not do this, it does not work. Name the variable SourceSQL. Press F4 again, set EvaluateAsExpression to TRUE and type the SQL statement that gets the data as the expression. Type everything on 1 line, for example: SELECT OrderBedrag, OrderNummer FROM FactOrders WHERE OrderDatumID = “ + (DT_WSTR,12)@[User::VandaagDatumID] .

Step 3

Go to the OLEDB Source in the Data Flow Task and set Data Access Mode to: SQL Command from Variable. Pick the variable below it. If you cannot pick a variable, the scope is not set correctly.

That’s it for now.