Dynamic Database connection in SSIS 2012


We have a few databases that have almost same tables with same structure, but with the data from different clients. The tables are created based off the data received from the clients. We cannot host the data for all clients in one database. Each one has to have a separate database. We have a separate VS 2012 solution that has a master package which in turn calls several SSIS packages to load the tables. We have one SSIS package per table,  as we have several validations that would be need to performed during the loads.
We are following project deployment model .We have Development, Testing and Production servers and we are using environment variables in the respective SSIS Projects.

However, I have a requirement such that a  VS solution that's created to load the data from one client can be reused for loading data from another client just by changing the database name in the connection and not the connection manager itself.This database connection needs to be changed manually, just at one place so that the packages
would point to the changed database name.

For example:  If Project A in solutionA  has a connection manager that points to DBName1 to load data from client1,when I reuse this solution to load data from Client2 with same table structures,we should change the name of the database DBName1 to  DBName2, just at one place and the Database in the connection manager should point to DBName2 instead of Database1.

Not sure, how to implement this. Any logic/code is greatly appreciated.

Thanks a million in advance! It's a bit urgent!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Use a variable and use that for the dynamic connection.
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Because you are using project deployment, you can even use a parameter which can be configured in the SSISDB Integration Catalog. In the connection manager, the connection string can be built using the server & database names coming from these parameters.

In fact, the official MS book on BI (for exam 70-463) has a similar scenario as one of the practice examples. In case you can't get the official book, here is a good post outlining how to use parameters to setup your connection managers (http://www.sqlchick.com/entries/2015/1/4/parameterizing-connections-and-values-at-runtime-using-ssis-environment-variables).

In order to cascade the values to your child packages, you will need to use parameters for the connection strings in the child packages too. Once parameterized, you will need to use package configurations to pass the values from the parent package to the child packages.
Megan BrooksSQL Server ConsultantCommented:
There are various ways to do it. Since you are using the project deployment model you have available to you project as well as package parameters. Parameter values can be used directly to specify portions of a connection string in a connection manager, or to specify the entire string.

I'm not sure exactly what you will need to do, but this example should serve to point the way. If you have a project-level shared connection manager then you can specify property values using expressions containing constants or project parameters. Properties of a package-level connection manager can be specified using expressions containing constants, package parameters, and project parameters.

In a simple case, you could define a DatabaseName project parameter, used to specify the database name (initial catalog) used by a project level connection manager (it could also be package level, since project parameters are accessible at both levels).

To do this, with the project open, you would double click Project params in Solution explorer to define the parameter. Here you can click Add Parameter, enter a name and type (String), and a default value (if desired). You can also specify whether the project parameter is required or not.

Next select the desired connection manager by single-clicking its name, and then display the properties (F4).

One of the properties is the Expressions collection. If you click it a button (...) appears that brings up the Property Expressions Editor when clicked.

Select the InitialCatalog property in the Property column of the dialog, if this is what you want to change dynamically at run time. Then on the right side of the row click the (...) button to bring up the expression editor and enter the expression that will determine the initial catalog (database name) property value.

In the expression editor, expand Project Parameters and then click on the parameter name you just created and drag it to the Expression box. You can click Evaluate Expression if you like, and then close everything. The database name is now determined by the project parameter that you defined.

One peculiarity of the SSIS designer is that you are going to need to have a valid database name in the connection manager itself at design time in addition to calculating the database name at run time. At run time, only the calculated value matters.

How you specify the database name parameter depends on how you are using the project deployment model. You can create environments for each database name, and different SQL Agent jobs that specify the different environments. Environments take some getting used to, but they can be quite useful.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

amuktaAuthor Commented:
Thank you very much Megan!However, I have a quick question! As mentioned by you in your post above,  we can have an expression for the initial catalog right, based on the  enviromment( Developement, testing and production) environment, the servername needs to change as well. Can we do the same for servername as well?

Thanks a lot in advance again for your help and your help so far!

Appreciate your help!
Megan BrooksSQL Server ConsultantCommented:
Sure, set the ServerName property using a different expression. The Expressions dialog allows any number of property rows and expressions. Or you can provide the entire connection string by setting just the ConnectionString property.
amuktaAuthor Commented:
Thank you! But ,I'm not able to provide system::servername as the expression in the servername property. Do we need to set the delay validation to false and retain same connection to true as well? all I see is just the Project parameters to pick from and there is no option to pick system::servername.

Best regards,
amuktaAuthor Commented:
Thank you Megan! But, Since, I cannot have an expression for project params and also since connections are defined at the project level, I'm not able to provide an expression as System::Machinename for the servername property.

Is there anyway we can do it without a hardcoded servername.
Pawan KumarDatabase ExpertCommented:
Yes you can pass server name also as a parameter


Are you aware of the script task or .Net programming.

Create a script task.
Use .Net code there.
Create the connection there based on a parameter value lets say a parameter a
If a = 1 the someconnectionsting , a = 2 anotherconnectionstring...

in .net it should be pretty easy.. try it out.
Megan BrooksSQL Server ConsultantCommented:
Property expressions can include available variables, package parameters, and/or project parameters. I offered an example using project parameters, but the same steps apply in other cases as well. If you want to use package level variables, however, you will need to define the connection manager at the package level (you can right-click the connection manager name and move it from project to package).

Once the connection manager resides at package level, system and user variables are available for use in property expressions, including System::MachineName. In the expression editor, available variable names can dragged from the top left panel to the expression, for convenience.
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
You can build your connection string using expressions that use variables. These variables can be populated via package configurations (which could be as simple as an XML configuration file). Depending upon your environment, you could supply different configuration files and the connections would realign accordingly. Since this is an extremely common requirement, it's pretty straight forward. Do give it a try on a test package.
amuktaAuthor Commented:
Thanks a lot! It not only resolved my issue, but made my life simple in incorporating dynamic connections in my project. Greatly Appreciate your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.