Edit SSIS Package Configuration in common for all the similar package

Question

Edit SSIS Package Configuration in common for all the similar package

MSSQLBuddy

SSCrazy

Points: 2354
More actions
February 10, 2009 at 3:35 am

#127455

Hi All,
Sorry if the topic already exists.
I have around 50 packages with the same connection string to the database.
The package configurations are from XML file.
Its highly impossible to change the connection string for the server name and the login credentials in all the package configurations, when deployed to production server.
Is there any way for changing the connection string in common, so that all the other package reflects with the same change in the connection string....?
Any help, greatly appreciated!
Thanks in advance.
Regards
Priya
Regards
Priya

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

SQLBuddy (2/10/2009)
Hi All,
Sorry if the topic already exists.
I have around 50 packages with the same connection string to the database.
The package configurations are from XML file.
Its highly impossible to change the connection string for the server name and the login credentials in all the package configurations, when deployed to production server.
Is there any way for changing the connection string in common, so that all the other package reflects with the same change in the connection string....?
Any help, greatly appreciated!
Thanks in advance.
Regards
Priya

You can store your connection strings in relational table and use them from there.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

You could have all these packages use a common configuration file.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

john.arnott SSChampion Points: 11882 More actions · Answer 3

As Alvin suggests, all packages can point to a common xml file. But beyond that, you can have them use more than one file. We do that for our appliction where we have the same half-a-dozen packages deployed to over 60 servers. Each server has a "Standard" xml config file with the unique connection string data for that server, then each package is run with a concatenation of that standard file and a package-specific file that usually has the same values regardless of server).

There are more sophisticated approaches to SSIS configuration management. I think Kirk Haselden's book on SSIS from SAMS publishing has an excellent section on the topic, covering the use of environment variables, xml files, registry entries, and configurations stored in SQL server.

MSSQLBuddy SSCrazy Points: 2354 More actions · Answer 4

Hi,

Thanks for your reply.

I have a common configuration file in C:\DBConfig\Databasename.dbConfig

I have pointed this config file for all the related packages using the same connection string.

But The Production server DBA's say that they might change the path from C drive to D drive as their servers might not contain C drives or so & so.

Also while deploying the package to production server, we copy only 4 files from the deployment folder..... (databasename.dtsConfig, packConf.dtsConfig, manifest file and the dtsx file)

How can we manage the path change from C drive to D drive of the database connection?

Also, at the time of deployment, How is the change in the common file path reflects in the package?

How will the change be applied to databasename.dtsConfig in the deployment folder from the common path C:\DBConfig\Databasename.dbConfig, when we change in the common path?

Please help......

Thanks in advance

Priya

Regards
Priya

john.arnott SSChampion Points: 11882 More actions · Answer 5

If I understand the situation, each package is expecting to find "C:\DBConfig\Databasename.dbConfig", but that file may now be moved (along with other things) to the D: drive. The key concept here is that the configuration file need not be specified inside the package, but may be part of the execution of the package. That is, you list the dtsconfig file(s) in the SQL scheduler or as a parameter to DTExec, however you're running the package.

So, when you move from C: to D:, you change the execution parameters in SQL Scheduler to let the packages know where to find the dtsconfig files, and you change the dtsconfig file to let the packages know that the database is now on D:.

One little caveat: even if the config files are not specified inside the package, the "use config files" option must still be turned on. Do that, and then leave the list in the package empty.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

We do not have to deal with different paths for the config file. All our server that hosts packages have a D: drive. With DTS we placed the file in D:\DTS\. Now with SSIS it's D:\SSIS\.

All your servers should have a C: drive, but whether or not you're permitted to use it or not is another question.

There's been numerous posting on how to deal with varying paths. One options uses a n environment on the machine. I'm not a big fan of this one because it may involve an group within the company and that's not always easy.

To add a little bit to what we do, this config file is only used for the common parameters. One item in this file is a connection string to a database that we use for storing the parameters required by the various package. All the parameters are stored in one table. The key columns in that table are the package name, parameter name, and parameter value.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

MSSQLBuddy SSCrazy Points: 2354 More actions · Answer 7

Thanks again. But the deployment folder of the package consists of the config file called databasename.dtsConfig.

Agian the common folder also has the same config file.

For Example,

If we change the database connection string in the common path, and add the same config file to the SQL Server Agent while configuring job, Does the databasename.dtsConfig in the deployment folder takes effect, as this file is also included in the package while deploying.....?

Please help in resolving this issue.....

Thanks in advance

Priya

Regards
Priya

MSSQLBuddy SSCrazy Points: 2354 More actions · Answer 8

Thanks a lot. I resolved this issue.

Just pointed the config file to the package in Agent configurations file.

Priya.

Regards
Priya

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 9

You're welcome and good luck

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

mobrechts yves Newbie Points: 3 More actions · Answer 10

You can store your connection strings in relational table and use them from there.

And how do you connect to THAT relational table ... if you do not have a fixed connection string in your package ?

John Jayaseelan Newbie Points: 7 More actions · Answer 11

Hi,

Even though I used different config file in the SQL job 'Configurations' tab, the Package still uses the variables from the config file that was specified during the deployment which is in the deployment folder.

Is there any solution to force the pkg to used the config file in specified in the 'Configurations' tab of the SQL job?

Thanks