SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Single config file for multiple SSIS packages


Single config file for multiple SSIS packages

Author
Message
Ashok Jebaraj
Ashok Jebaraj
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 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
Justin Lezon
Justin Lezon
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1229 Visits: 959
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
Ashok Jebaraj
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 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
Tony Mungor
Tony Mungor
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 240
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.
Tom Van Harpen
Tom Van Harpen
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2238 Visits: 1115
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
ingoba.ningthouja
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23830 Visits: 5314
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
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17014 Visits: 7413
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”
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23830 Visits: 5314
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
Ed Zann
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1974 Visits: 1398
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search