Sql-server – Changing database name based on project environment

sql serversql-server-2012ssis-2012synonyms

I have three environments for our BI project (DEV, QA, PROD) and two databases in each environment, source(EDB) and target(EDM)

  • DEV environment: Source = EDB_DEV Target = EDM_DEV

  • QA environment: Source = EDB_QA Target = EDM_QA

  • PROD environment: Source = EDB Target = EDM

SSIS OLE DB Source component have connection to EDB_DEV but query also refer to EDM_DEV

SELECT A, B, C
FROM tblFirst F --(table in EDB_DEV)
INNER JOIN EDM_DEV.dbo.tblEDMTable T ON T.ID = F.ID

Now I have to migrate to QA or PROD environment. So I am wondering, is there any way I can dynamically tell which database to go based on connection/environment.

For e.g EDM_DEV in query should be replaced with EDM_QA when running in QA environment.

Best Answer

You can use variables/parameters in your SSIS package to dynamically build your queries, probably the easiest method. It might be tedious to get setup based on how many queries you have used everywhere in your package. Just have something like an "User::Environment" variable that holds either: _DEV, _QA or _PROD. You then join that into your variable holding your query and then as you change that value it would query the appropriate table.

You could base it on the server you are connecting to as well, but would be the same premise of having to configure your queries dynamically in a variable + expression setup. I would probably use above method over validating against server I'm connecting to, just to have more precise control.

Related Question