SSIS: Queries With Different DB Schema Based on Environment

infotechelg used Ask the Experts™
In SSIS, I am using project level paramaters to choose ODBC drivers based on if the package has been deployed to Development or Production.

However, the SCHEMA on each server is DIFFERENT. For example, in development the schema is "DVDTA" and in production it is "PRODDTA". So, when querying a table in dev, it'd look like this: SELECT * FROM DVDTA.Users, and in prod: SELECT * FROM PRODDTA.Users

It does not appear I can set the schema in the DSN itself, and it does not appear I can create a SCHEMA parameter in SSIS, and then do something like this in the "ODBC source" data flow: @[$Project::Schema].Users

So, in SSIS, how would I go about dynamically writing the query correctly based on which environment the package is deployed to?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can have the query in a variable that is populated by a string expression.
The expression can be a string like:

"SELECT * FROM " + @schemaName + ".Users;"

where @schemaName is also another variable that is switched based on the environment.


Thanks, Nakul!


Nakul, this works if i'm doing an OLE DB Source connection, as "SQL command from variable" is an option in the drop down. But how do I do this for an ODBC Source Connection?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial