2

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?

Improve this question
2
  • 2
    Must you use the use statement in your Execute SQL Tasks?
    – billinkc
    Nov 8, 2013 at 20:39
  • 1
    If your SQL can reference three-part objects inside of a common database, you could use synonyms to temporarily point them to the right place.
    – Aaron Bertrand
    Nov 8, 2013 at 20:47

1 Answer 1

Reset to default
0

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?

Improve this answer
2
  • Add some of the details to your answer just in case the link goes stale.
    – Hannah Vernon ♦
    Nov 9, 2013 at 23:31
  • The connection string and be built by using values stored in variables. Its a more complicated and requires a rather complex expression. An added option is to stored the variables in a configuration file or database table means do not even have to open the SSIS package to change the connection string.
    – SQLBobScot
    Nov 10, 2013 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.