How to set database name as a variable in SSIS?

variables ssis oledbconnection
Echo picture Echo · May 8, 2012 · Viewed 8.3k times · Source

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

Diego picture Diego · May 9, 2012

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

enter image description here

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.

Related questions

SSIS set variable at runtime

Anyone know how I can change a SSIS variable at runtime? I have a variable User:SkipStuff I want to set this based on a condition during a for loop container

Read More
SSIS Result set, Foreachloop and Variable

I'm having an issue and I don't know if this is possible to do. I have a simple SQL task. SELECT Name, email FROM table_name Using a result set, I'm trying to pass it to a variable and use …

Read More
SSIS Element cannot be found in a collection (but I have them all listed!)

I'm getting a persistent error: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. …

Read More