I was recently working with FoxPro for Unix as a data source using ODBC in SSIS (story for another day) and found some interesting scenarios inside of it. We wanted to pull the table name dynamically, thus changing the SELECT statement inside the SSIS Data Reader Source. Whilst this is quite easy in OLE DB Sources, there's no obvious way to do this in the Data Reader Source for ODBC or legacy connections as you can see in the below screenshot.

There's still a workaround, which feels like a hack but it works great. Before you start, change the name of the Data Reader Source to something that you're fine leaving it as because you're about to create a dependency on this name. To make the query dynamic from an SSIS Data Reader Source or ADO.net Source, select the Data Flow Task and in the Properties window, you'll see the name of the Data Reader Source with your query inside of it as shown in the below screenshot. This shows you which property you want to make dynamic, which in my case is the [DataReader Source].[SQLCommand] property. You then want to set the expression on that property by clicking the ellipsis button next to the Expression property. Lastly, create an expression like this, which makes the query dynamic using a variable:

"SELECT * FROM "  @[User::TableName]

You may find yourself needing to turn off the ValidateExternalMetadata property if don't want your query to parse until execution instead of during the validation stage. You can set many properties this way in the data flow to be dynamic but it does require the names of the components you're setting to be static.

Brian Knight