SSIS packages are handy. They can do all sorts of things and the developers here use them a lot. One very useful feature is that you can set up connection objects in your package so it can connect to any of your servers to do what it needs to do. This is also one of their drawbacks.

With normal T-SQL code, in most cases, if you know what database the code runs against, you know where the code lives. Linked servers are an exception, but as a general rule, if the code runs against Database A on Server X, then the code typically resides somewhere on Server X.

But you can make no such assumptions with SSIS packages. Because they can contain their own connection objects to other SQL Servers, they could run on any of the servers in your environment. At my location, we try to put our packages on SQL Servers dedicated to hosting and running SSIS packages, but we do have some packages running on various other servers.

Now add in the fact that, as time goes on, peope leave the company and new people join and you’ve got a perfect recipe for completely losing track of where SSIS packages are stored.

Luckily, there is a way to search a server for SSIS packages. In fact, you can search for SSIS packages containing a specific text string  – a server or database name, for example. Very handy if looking for any packages that touch a particular database. Here is some code in two versions – one for SQL 2008 and later servers and one for SQL 2005 servers. Combine these code snippets with SSMS’s ability to run queries against multiple servers at once, and you can quickly search your entire SQL Server environment for a particular SSIS package.

Note that this code will only work for SSIS packages that are stored within SQL Server. If you store your packages in the file system, this method will not work.

-- SQL 2008 and later
WITH    cte
          AS (SELECT    name AS [ssis name]
                       ,ssistxt = CAST(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML)
              FROM      msdb.dbo.sysssispackages
             )
     SELECT *
     FROM   cte
     WHERE  CONVERT(VARCHAR(MAX), ssistxt) LIKE '%SearchString%'

-- SQL 2005

WITH    cte
          AS (SELECT    name AS [ssis name]
                       ,ssistxt = CAST(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML)
              FROM      msdb.dbo.sysdtspackages90
             )
     SELECT *
     FROM   cte
     WHERE  CONVERT(VARCHAR(MAX), ssistxt) LIKE '%SearchString%'