Single config file for multiple SSIS packages

Question

Single config file for multiple SSIS packages

Ashok Jebaraj

Ten Centuries

Points: 1215
More actions
February 25, 2009 at 4:27 am

#128028

Hi,
is there a way we can use a single configuration file and deploy several SSIS packages. I have a solution which has may SSIS packages. Most of the DB connection strings are similar but every DTS has it's own set of configurable attributes. I was able to deploy the ssis package one by one using one/more config file for each SSIS package. But how can we use one config file for multiple SSIS packages?
Thank you,
Ashok Jebaraj
May 26, 2010 at 6:28 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

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

Justin Lezon SSCommitted Points: 1937 More actions · Answer 1

You can do this. Just enable configurations for each package. Create a configuration file through one of the packages. Then add that configuration file to each package that uses the same connection string information stored in the configuration.

Ashok Jebaraj Ten Centuries Points: 1215 More actions · Answer 2

Hi,

You are right. Say for example I have a solution with 2 packages. The first one pulls data from XL to SQL Server database and the second one pulls data from XML to the same SQL Server database. In that case I cannot use the same configuration file for the second package because I will not be able to edit the config file when I configure the settings for the second file? Please let me know if tyou need more explaination? Any hel appreciated.

Ashok Jebaraj

Tony Mungor SSC Eights! Points: 850 More actions · Answer 3

I use variables with different names to store my file names in. Then the config file has an item for each variable. You do not need to have every variable defined within each package, you just need to use unique variable names so they are stored seperatly in the config file. Then I use expressions using the variables to configure my different nodes/items within my packages.

TangoVictor SSChampion Points: 10975 More actions · Answer 4

when you create a config that will be reused you must have exactly the same items in both packages. whether they are variables, connection managers, etc.....

build the config in one package then use it in the second by adding and selecting "Reuse Existing"

You can't edit the config in one package then use it in the other it will fail.

If you want to add a connection manager in one package and use configs for it you must add it in the second package but of course you don't need to use it, just make sure it has the same name.

One technique that I use is to build every package with set of default connection managers one for logging operation one for calling shared procedures, and I like to have a generic one that I can wire up later if needed.

Then I use a global shared config for all of them. I also use a trigger on the SQL table to prevent updates.

ingoba.ningthouja Valued Member Points: 51 More actions · Answer 5

Hi Guys,

Is it possible to share one config file across multiple packages in SSIS 2005? I am getting error while building the package. Please help.

Thanks,

Ingoba

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

I pretty much what the poster before the last post does. I take it a step further and mark the file read-only so that I have to go out of my way to accidently change it.

Also, what error? That is like taking a car to a mechanic and saying it isn't working right..

CEWII

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

As far as templates I am generally for them, however read this article and decide for yourself:

http://bi-polar23.blogspot.com/2009/09/ssis-package-templates-good-bad-and.html

CEWII

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 8

We run dozens of SSIS jobs daily. I use the [SSIS Configurations] table to store all of our package configs. This works well for us. Everything is in one place and it is easy to update. Also we use a specific naming convention with our package variables across all of the packages. For instance we have a standard variable called BFP1 which contains the "base file path" or root folder to which many of our SSIS data extracts are written. Should we ever decide to move that location, it is as easy as a simple update query where the variable name = BFP1 and all affected packages will be updated.

pkrudysz Right there with Babe Points: 772 More actions · Answer 9

Elliott,

The error that ingoba.ningthouja might be getting (and I am getting) is "the file ... already exists". This happens during build when CreateDeploymentUtility is set to true, and 2 or more packages are set to use the same Package Configuration File. It happens because SSIS is trying to create the same file twice. How can we avoid that? (and still be able to have 2 pckgs use the same config file)? Tx.

maruthi.vm SSC Rookie Points: 31 More actions · Answer 10

Any solution for your problem. I am also facing the same problem please let me know,thanks in adavcne 🙂

pkrudysz Right there with Babe Points: 772 More actions · Answer 11

Package Configuration files kinda suck. You're much better off using environment variables for configuration - no issues with the deployment. That's what I did. 🙂

MazeRunner Grasshopper Points: 11 More actions · Answer 12

Can anyone please give an example of this topic. I have 10 packages and each package has multiple variables being used.

How could I have one xml configuration file for all the packages ? Thanks in advance.

SQL.AP Hall of Fame Points: 3244 More actions · Answer 13

Yes, you can use one config file in multiple packages. Just make sure config file name is same in all the packages.

Packages will automatically pick the config values available for their variables.

____________________________________________________________

AP