How to set database name as a variable in SSIS?

Question

In my current project, the database name keeps changing and I do not want to modify my OLE DB Sources each time. Thus I want to set it as a variable. That is, in each OLE DB Source, I could use it as a parameter. However, it seems SSIS doesn't allow database name to be transferred as a parameter. So what should I do to minimize my changes?

I wish to do something like this SQL statement in OLE DB Source:

Select OrderID
  From ?.Order

And I just need to modify my variable when the database name is changed. Thanks for any solutions!

Answer 1

Check out this terrific post on how to parameterize the database name. This a very thorough blog post on the topic of parameters and environments in SSIS.

http://www.sqlchick.com/entries/2015/1/4/parameterizing-connections-and-values-at-runtime-using-ssis-environment-variables

Answer 2

A couple of suggestions for you:

• http://social.msdn.microsoft.com/Forums/en-us/sqlintegrationservices/thread/586bf0a7-16c1-4a24-a9b2-7b8fb0cffda9
• http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/b72e1b45-9a78-4865-912a-4587fa72c7f7

Answer 3

I like to manage Server\Instance.Database at the connection manager and Schema.Table or View at the task or adapter level. I recommend externalizing connection manager connection strings. You can use Package Configurations to do this, or you can manage it yourself (I use a table with ConnectionName and ConnectionString columns, and encrypt the ConnectionString column). In my opinion, a hard-coded database name inside an SSIS package is not a best practice.

I recently recorded a couple sessions on SSIS Connections management - one for 2005, 2008, and 2008 R2; another for 2012. I have also recorded a session on managing configurations in SSIS (although the audio is poor).

I hope these help.

Andy

Answer 4

you need to add package configurations to your package and set the database name of the connection manager as one of the configurations

Just right click any blank area on the control flow and select "package configuration" and follow the wizard.

I Suggest you start with a XML configuration because its easier to understand.