I reckon that one of the most commonly used components in SSIS is the OLE DB Source component. This is the one that you use to pick up data from an OLE DB compliant data source (e.g. a database) in order to use that data in your pipeline. This is done with a SQL statement.
One of the more common usage scenarios is to dynamically generate the SQL statement at runtime. For example you may have a WHERE clause in that SQL statement in which you want to use different values.
BOL probably states that the way to do this is use a parameterised SQL statement. Well...don't listen to BOL. Parameterised SQL statements are difficult to build and are subject to the vagaries of OLE DB Providers (see Kirk's post on the subject here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx).
The alternative is to build your SQL statement dynamically in a variable and use that in your OLE DB Source component. Here are the steps for the WHERE clause scenario mentioned above:
- Create a new variable called SourceSQL
- Open up the properties pane for SourceSQL variable (by pressing F4)
- Set EvaluateAsExpression=TRUE
- Set Expression="SELECT * FROM MyTable WHERE MyColumn = " + @[VariableContainingFilterValue]
- For your OLE DB Source component, open up the editor
- Set Data Access Mode="SQL Command from variable"
- Set VariableName = "SourceSQL"
So the only thing you have to do after that is find a way of changing @[VariableContainingFilterValue]. That's not within the scope of this post because there are many many ways that you could do this. I'll leave it to your imagination!
P.S. This technique works equally well on the SQLStatementSource property of an Execute SQL Task.