Sql-server – Namespacing database names on the server in order to test multiple different SSIS packages

sql serverssisssis-2012

An SSIS package that I'm building has two OLE DB connections, to databases named "core" and "staging". I created test versions of these two databases on my local SQL Server installation. I named them "test1_core" and "test1_staging". The "test1_" namespace is necessary because I have many SSIS different packages in active development at one time, and many of them refer to the same production databases. But it need each local SSIS package to refer to its own version of the database.

This works okay until I need to refer to the database name in the package specifically like in an Execute SQL task that includes two connections.

Also, I have SQL scripts that say use [core] at the top and I wish I could just use them on my local server without always having to open them up and change that line to use [test1_core] first, then having to change it back.

Is there some way to namespace the database name from within SQL Server so I can just refer to the local database as "core" from the SSIS package?

Best Answer

When l was testing a large SSIS package l switched the data connections using the first technique here. The second technique in the second answer l have not tried but looks simpler to implement. The main advantage was that l could easily switch between test and production. Just wondering if that is easier than changing the namespace?