posted 10/3/2010 by timmurphy - Views: [5836]
If you need to use an ADO.NET source you will discover that the Data access mode only gives you a choice of Table or view or a SQL command but no choice of SQL command from variable like there is in an OLEDB connection.
There is a way to make the SQL command dynamic. In the Control Flow Tab, select the Data Flow Task that contains the ADO.NET Source and press F4. This will bring up the properties window for the DFT.
Click the expression button, opening the Property Expressions Editor, and select SQL command for the property.
Click the expression button to open the Expression Builder.
Proceed to build the expression using the tools and/or by typing the code. In this example, the variable is user defined, but you could also use a system variable. The tricky part is making sure you have double quotes all in the right places and have the correct variable type because the expression must evaluate to a string (another blog, another day).
Be sure to Evaluate Expression to see if it indeed looks like an executable SQL command.
Note: If you define a variable you might need to place a proper default Value in the variable so the SQL command will allow validation of the metadata.
Have fun!