Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSIS object search using T-SQL

By James Greaves,

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

Total article views: 7893 | Views in the last 30 days: 43
 
Related Articles
BLOG

Searching SSIS Packages or searching XML In General

Today I'm posting a small script with a simple purpose. I wanted to search all of my SSIS packages i...

FORUM

Search DTS Package

Search DTS Package

FORUM

Call 3rd party software within package

Call Address Cleansing Tool within package

FORUM

Fulltext Search within Results

i want to do nth fulltext search within result set of fulltext query? but I cannot get a good idea ....

SCRIPT

String Search

Search for a string value within columns of data types CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, ...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones