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

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.

This entry was posted in Utilities and tagged , . Bookmark the permalink.

Leave a Reply

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