This article outlines how to quickly search for text and objects within SSIS packages that are stored in the MSDB database with the SQL Server Management Studio (SSMS) query window. In my current environment, we are commonly faced with questions regarding the content of SSIS packages that may or may not contain an object name or script. This helps us determine if the package may require to be changed. Within this article I will outline a few scenarios to quickly search the contents of any SSIS package(s) stored within the MSDB database so you do not have to use a Visual Studio BIDS session to download the package from the server, open the package, and perform the "view code" on the package, and search for a specific object name.

The first step is to setup SSMS to increase the XML output size from the default 2 MB to unlimited. This change will allow you to see complete contents of the XML data that represents the SSIS package within MSDB. Within SSMS on the menu tool bar select " Tools --> Options --> Query Results --> Results to Grid ". See the image below for the setting change for before and after.

Below are some examples of the types of searches you can do within an SSIS package for specific objects.

Search 1: A simple SSIS package search for Visual Basic Script within the SSIS package - This is performed using the CONVERT() function in the WHERE clause to convert the "packagedata" column from the msdb.dbo.sysdtspackages90 table to a format that string matched in the where clause. This method I find quite useful for a quick search of my SSIS packages.

SELECT [name] AS SSISPackageName
, CONVERT(XML, CONVERT(VARBINARY(MAX), packagedata)) AS SSISPackageXML
FROM msdb.dbo.sysdtspackages90
WHERE CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), packagedata)) LIKE '%sub main()%'

Search 2: SSIS Package search for a particular object using a temporary table - In the example below I use the temporary table to store the packagedata column from the msdb.dbo.sysdtspackages90 table so I can use the "SSISPackageVarchar" column to a string match for any database object that I am looking for.

SELECT {name] AS SSISPackageName
, CONVERT(XML, CONVERT(VARBINARY(MAX), packagedata)) AS SSISPackageXML
, CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), packagedata)) AS SSISPackageVarchar
INTO #SSISObjectSearch
FROM msdb.dbo.sysdtspackages90
SELECT *
FROM #SSISObjectSearch
WHERE SSISPackageVarchar LIKE '%<objectName>%'

Search 3: SSIS Package search using a temporary table and a specific folder name - On our SSIS servers we use child folders beneath the default "MSDB" folder to organize our packages. In the example code below we join to the msdb.dbo.sysdtspackagefolders90 to find the folder names that are encoded in the msdb.dbo.sysdtspackages90 table. Packages that are not stored within a folder have the default value of "00000000-0000-0000-0000-000000000000" in the folderid column that is a uniqueidentifier data type withinmsdb.dbo.sysdtspackages90 table.

SELECT {name] AS SSISPackageName
, CONVERT(XML, CONVERT(VARBINARY(MAX), packagedata)) AS SSISPackageXML
, CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), packagedata)) AS SSISPackageVarchar
, SF.FolderName
INTO #SSISObjectSearch
INTO ##SSISObjectSearch
FROM msdb.dbo.sysdtspackages90 AS SP
INNER JOIN msdb.dbo.sysdtspackagefolders90 AS SF
ON SF.FolderID = SP.FolderID
WHERE SF.FolderName = '<object_name>'
SELECT *
FROM #SSISObjectSearch
WHERE SSISPackageVarchar LIKE '%<objectName>%'

The result sets that are produced by the queries above will have a "SSISPackageXML" column that you can click on and open a new query analyzer window to see the complete XML of the SSIS package. From the new window you can perform a search for all the specific string occurrences or export the XML to a file name of your choosing to examine with the XML tool of your choice.

I hoped you enjoyed reading this article and please feel free to leave any comments/questions on the message board. Thanks for reading.

References

• sysdtspackagefolders90 MSDN article
• sysdtspackages90 MSDN article