February 8, 2012 at 9:14 am
Hi, I have one SSIS package that is run against many databases (live, test, training, development, seven in all). I have copied this package seven times, each is exactly the same except for the database connection (different databases on different servers). The package also references files located on different file servers depending on which database is being used.
Maintaining this package is time consuming as each and every change has to be replicated seven times.
Is it possible to have a single package that has the database name passed to it at run time? Giving me single package to maintain.
I can get over the different file locations by passing variables from the SQL Agent Job to the package at runtime, it is getting the package to connect to the right database each time that is proving the problem.
Any ideas, pointers, comments will be gratefully received.
Thank you in advance
February 8, 2012 at 9:19 am
Just as you pass in different values for the file location in the job step you can change the connections strings also. Try updating the value in the Data Sources tab to the relevant database.
February 8, 2012 at 9:58 am
Create a variable called server (or similar) and a variable called database. Give them a default value so you can run the package as debugged.
Modify your database connection to use an expression and assign the variable to the approriate place.
These variables can then be passed directly into the package.
I found this link. The method I'm thinking of is from the sql agent job.
Expressions are pretty common througout the whole of ssis packages. They make it very dynamic and able to change during runtime.
February 10, 2012 at 4:11 am
February 10, 2012 at 5:16 am
Package configurations are created for just this purpose:
Understanding Integration Services Package Configurations
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 10, 2012 at 8:49 am
Koen Verbeeck (2/10/2012)
Package configurations are created for just this purpose:
Package configs a bit too finicky for my liking and distribute the information into another location. Keeping it in a job keeps it centralised and makes it possible to have multiple identical steps.
I dont recall you being able to have multiple servers in a single file so you would need multiple configuration files and multiple jobs steps. A bit messy.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply