Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Efficient way to migrate over 100 SSIS packages

Author  Topic 

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2011-11-03 : 14:59:42
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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 05:24:29
why should you do it manually. the best way was to add package configurations inside packages to store configuration values onto a sql tasbl/xml file. then property values can be changed within this table/file after moving packages to new server to point to correct db tables/filepaths

see
http://www.sql-server-performance.com/2007/package-configuration-2005/
http://www.sqlis.com/post/Easy-Package-Configuration.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -