In this article, we will explore how to quickly search for text and objects within SSIS packages that are stored in the MSDB database using the SQL Server Management Studio (SSMS) query window. This can be particularly useful when you need to determine if a package requires any changes without having to download and open it in Visual Studio BIDS.
The first step is to configure SSMS to increase the XML output size from the default 2 MB to unlimited. This change will allow you to see the complete contents of the XML data that represents the SSIS package within MSDB. To make this change, go to “Tools” > “Options” > “Query Results” > “Results to Grid” and select the “Unlimited” option.
Now, let’s look at some examples of the types of searches you can perform within an SSIS package:
Search 1: Searching for Visual Basic Script within an SSIS Package
To search for a Visual Basic Script within an SSIS package, you can use the CONVERT() function in the WHERE clause to convert the “packagedata” column from the “msdb.dbo.sysdtspackages90” table to a format that can be string matched. Here’s an example query:
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: Searching for a Specific Object using a Temporary Table
If you are looking for a particular database object within an SSIS package, you can use a temporary table to store the “packagedata” column from the “msdb.dbo.sysdtspackages90” table. This allows you to perform a string match on the “SSISPackageVarchar” column. Here’s an example query:
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: Searching for a Specific Folder Name using a Temporary Table
If your SSIS packages are organized into folders, you can search for a specific folder name by joining the “msdb.dbo.sysdtspackages90” table with the “msdb.dbo.sysdtspackagefolders90” table. Here’s an example query:
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
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 produced by these queries will include a “SSISPackageXML” column. You can click on this column to open a new query analyzer window and view the complete XML of the SSIS package. From there, you can perform further searches or export the XML to a file for further analysis.
We hope you found this article helpful in quickly searching for text and objects within SSIS packages using SQL Server Management Studio. If you have any comments or questions, please feel free to leave them below.
Thank you for reading!