Introduction
If you are using SQL Storage for your SSIS packages and have multiple folders to make life easier then there is no doubt you have run into this before. A package gets moved to production in what appears to be the right folder but the requestor reports that the old package is still running. You move the package again and still no change. Eventually you realize that you have duplicate packages in different folders on your server and set out about the painful task of opening each folder, hunting for other copies of the package. This is not so bad if you have 2-3 folders but the more complex your environment the more painful this becomes. Having run into this on more than one occasion myself I decided to write a script to do the work for me.
The Script
This is another easy one so I will not spend too much time on the mechanics of it. Get all packages, grouped by name where COUNT(*) > 1 then dig for the folder names and concatenate them into a nice string. I chose to create a folder name list because it makes it easy to create a report that can be emailed from the server. This script does not help if the packages are not named the same but at that point the problem should be pretty obvious to the person trying to run it.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT p.name AS package_name, REPLACE(RTRIM(( SELECT pf2.foldername + ' ' FROM msdb.dbo.sysdtspackagefolders90 pf2 INNER JOIN msdb.dbo.sysdtspackages90 p2 ON pf2.folderid = p2.folderid WHERE p2.name = p.name ORDER BY pf2.foldername FOR XML PATH('') )), ' ', ', ') AS folder_names FROM msdb.dbo.sysdtspackages90 p GROUP BY p.name HAVING COUNT(*) > 1 |
Conclusion
As usual, I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.