Solved

Dynamic Database connection in SSIS 2012

Posted on 2016-09-29
11
449 Views
Last Modified: 2016-10-17
Hi!

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!
0
Comment
Question by:amukta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41822864
Use a variable and use that for the dynamic connection.
0
 
LVL 14

Expert Comment

by:Nakul Vachhrajani
ID: 41822872
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.
0
 
LVL 16

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 41822899
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:amukta
ID: 41825569
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!
0
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 41825698
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.
0
 

Author Comment

by:amukta
ID: 41826992
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,
0
 

Author Comment

by:amukta
ID: 41827157
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.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41827247
Yes you can pass server name also as a parameter

@Author

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.
0
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 41827343
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.
0
 
LVL 14

Expert Comment

by:Nakul Vachhrajani
ID: 41827420
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.
0
 

Author Closing Comment

by:amukta
ID: 41847465
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!
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question