amukta
asked on
Dynamic Database connection in SSIS 2012
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!
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!
Pawan Kumar
Use a variable and use that for the dynamic connection.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thanks a lot in advance again for your help and your help so far!
Appreciate your help!
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.
ASKER
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,
Best regards,
ASKER
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.
Is there anyway we can do it without a hardcoded servername.
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.
@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.
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.
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.
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.
ASKER
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!