List of SSIS Packages based on keyword search

SQL Query to find the SSIS Packages with wildcard search for specific keyword.

For SQL Server 2008 and above

SELECT name
,CAST(CAST([packagedata] as varbinary(max)) as xml) AS PackageSource
FROM [msdb].[dbo].[sysssispackages]
WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))
LIKE '%keyword%'

For SQL Server 2005:

SELECT name, CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))

FROM msdb.dbo.sysdtspackages90

WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))

LIKE '%keyword%'



SELECT name
,CAST(CAST([packagedata] as varbinary(max)) as xml) AS PackageSource
FROM [msdb].[dbo].[sysssispackages]
WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))
LIKE '%keyword%'

For SQL Server 2005:

SELECT name, CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))

FROM msdb.dbo.sysdtspackages90

WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))

LIKE '%keyword%'