Solved

Dynamic Database connection in SSIS 2012

Posted on 2016-09-29
11
68 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
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 18

Expert Comment

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

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 13

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
 

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 13

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 18

Expert Comment

by:Pawan Kumar Khowal
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 13

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 11

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
INSERT INTO SELECT JOIN THING 2 27
Recurring Excel Timelime for Veeam 2 37
Sql query 34 22
login and database user 3 21
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now