Unanswered: Migrate over 100 SSIS packages quickly and efficiently into a new solution
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.
I haven't tried this myself so I can't say whether or not it will work - but you could just copy the dtsx files from DB_A, open them up in notepad or your favourite text editor and change the properties.
For the folder it loops through, look for a property called <ForEachFileEnumeratorProperties> and for the connection string, <DTS:Property DTS:Name="ConnectionString">
Save that then load into DB_B... try it with one of them and see what happens. Can't guarantee it will work but worth a try.