asked on SSIS: Queries With Different DB Schema Based on Environment
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?
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?
* ODBCSSIS
Last Comment
infotechelg
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks, Nakul!
ASKER
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?