Single config file for multiple SSIS packages

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Single config file for multiple SSIS packages...

13 posts, Page 1 of 2

1 2 »»

Single config file for multiple SSIS packages

Rate Topic

Display Mode

Topic Options

Author

Message

Ashok Jebaraj

Posted Wednesday, February 25, 2009 4:27 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 1:31 PM
Points: 33, Visits: 139

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

Post #664110

Justin Lezon

Posted Wednesday, February 25, 2009 5:01 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:41 PM
Points: 1,101, Visits: 843

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.

Post #664128

Ashok Jebaraj

Posted Monday, March 02, 2009 1:29 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 1:31 PM
Points: 33, Visits: 139

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

Post #666400

Tony Mungor

Posted Tuesday, March 03, 2009 12:09 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:02 AM
Points: 112, Visits: 235

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.

Post #667566

Tom Van Harpen

Posted Tuesday, March 03, 2009 2:06 PM

Old Hand

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:15 AM
Points: 386, Visits: 901

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.

Post #667646

ingoba.ningthouja

Posted Tuesday, May 25, 2010 6:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 14, 2010 3:46 AM
Points: 1, Visits: 35

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

Post #927405

Elliott Whitlow

Posted Tuesday, May 25, 2010 8:58 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

Post #927543

Stewart "Arturius" Campbell

Posted Wednesday, May 26, 2010 6:28 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 3,129, Visits: 4,312

One way to make this easy is to build templates, distributed to all developers, which incorporate the most frequently used connections, with config files created for each.

the advantage of this is that, one config file could be used by numerous SSIS packages, and one SSIS package can be run numerous times, each one with a different config file.

The setup I have one the servers is one folder for the generic config files, and one folder for package specific files.
This makes config file management simple.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #928172

Elliott Whitlow

Posted Wednesday, May 26, 2010 10:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

Post #928422

Ed Zann

Posted Thursday, May 27, 2010 7:44 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 7:05 AM
Points: 470, Visits: 1,054

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.

Post #928977

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions