How Do I Find SSIS Packages that Exist in Multiple Folders?


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.

SELECT AS package_name,
                SELECT      pf2.foldername + ' '
                FROM        msdb.dbo.sysdtspackagefolders90 pf2
                            INNER JOIN msdb.dbo.sysdtspackages90 p2
                                ON pf2.folderid = p2.folderid
                WHERE =
                ORDER BY    pf2.foldername
                FOR XML PATH('')
            )), ' ', ', ') AS folder_names
FROM        msdb.dbo.sysdtspackages90 p
HAVING      COUNT(*) > 1


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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.