How do I pass a variable to ODBC SQL Command in BIDS?

Question

I'm trying to get the variable as a parameter in my SQL command in ODBC source.

I've created the query in expression but how do I create the query in ODBC SQL Command Section.

Answer 1

The ODBC source SqlCommand can be set with an expression at the data flow level:

1. In the data flow, click on the background and hit F4 for the properties of the data flow.
2. In the properties window, scroll to expressions
3. Click on the ... and choose the property to set, i.e. [ODBC Source].[SqlCommand]

4. Click on the ... in the Expression column and add an expression like:

   "SELECT * FROM MyTable WHERE id = " + @[User::MyVariable]