Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Single config file for multiple SSIS packages Expand / Collapse
Author
Message
Posted Wednesday, February 25, 2009 4:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, February 25, 2009 5:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:59 AM
Points: 1,101, Visits: 932
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
Posted Monday, March 2, 2009 1:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, March 3, 2009 12:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 3:33 PM
Points: 112, Visits: 239
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
Posted Tuesday, March 3, 2009 2:06 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 6, 2014 7:07 AM
Points: 412, Visits: 980
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
Posted Tuesday, May 25, 2010 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, May 25, 2010 8:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Posted Wednesday, May 26, 2010 6:28 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 4,107, Visits: 5,469
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
Posted Wednesday, May 26, 2010 10:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Posted Thursday, May 27, 2010 7:44 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:50 AM
Points: 487, Visits: 1,239
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 »

Add to briefcase 12»»

Permissions Expand / Collapse