November 3, 2011 at 12:55 pm
Hi guys,
I have the following question:
I have over 100 SSIS packages. Each package loops through all the csv files in a specific folder (let's call it Folder_A) and loads the data into a specific base table in a database (let's call it DB_A).
I now have another database (DB_B) with identical structure, but a different name.
I have another folder (Folder_B) with csv files with identical column structure as in Folder_A, but different data. This data needs to be loaded into DB_B.
One approach is as follows:
1. Create a new solution
2. Add existing packages (one by one)
3. Open each package and change the 'Connection Manager' to point to DB_B, and the 'Foreach' loop task Collection Folder to point to Folder_B.
4. Run the packages and they should work
The problem is that there are over 100 packages, so to do this manually for each one will take time, and I'm trying to figure out whether there is a faster way to do this (both migrate over 100 packages into a new solution, and change connection string + folder location), especially since I would need to do the same for many other databases.
Please advise.
Thank you!
November 3, 2011 at 1:33 pm
You can use an XML configuration file to be read at runtime.
So all you would have to do is change the configuration file to point to a different database.
The initial setup would be time consuming as you would have to edit all 100 packages to read from the configuration file.
-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer
November 3, 2011 at 1:33 pm
Not a SSIS developer, but I can give you some insight from the database adminstrative side.
I know that my environment, developer use "Set Values" inside of the SQL Agent job sets which call SSIS pacakges. The set value is passed into the package and "sets" the value of variables. They then define the hostname to use in their connections as a variabel. That way they can develop package against dev, then whet it moves to staging/prod, we just change the 'set value' of the variable that the jobs passes to the package to use in the connection. It is easy to change these en mass (say for a server migration) by updating the command column of the jobsteps table.
If your packages arent developed this way, this might not be an option at this time, but if you are going to have to change them anyways, this might be an approach to look into so that next time you are not in the same boat.
November 3, 2011 at 1:38 pm
I have to agree with modifying the SSIS packeages to be dynamically configured for SOURCE and DESTINATION. If the only thing changing in the packages is that, nothing else, why create 100's of new SSIS packages that do the same thing?
November 3, 2011 at 1:44 pm
depending on the requirments, you could even look at a list of sources and destinations and wrap the whole thing in a for loop container.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply