Search the contents of SSIS packages

SSIS packages that are deployed to SQL Server or SSIS package Store are stored in msdb..sysdtspackages90 table. Not only SSIS packages but the Maintenance plans are also stored in this table.

Now suppose you are asked to list out all SSIS packages and Maintenance plans:

That update dbo.Sales table (to check dependency of a table).

That use the script file located at “D:\FTP\” folder (to check dependency of a file).

Those fetch data from database SourceDB or use login UserA (to check dependency of a source or user).

If you have not more than a dozen packages than you can check by opening each one in BIDS but if there are hundreds of packages than this approach is a very troublesome.

The better approach is to list all packages in which the particular table, connection or login is used. That would shorten the list of packages that you need to open for more details. For this purpose you can use the msdb..sysdtspackages90 table as below:

 

SELECT [name] 

FROM msdb..sysdtspackages90

WHERE CONVERT(VARCHAR(MAX), CONVERT (VARBINARY(MAX), packagedata))

      like '%dbo.Sales%' --change the literal for your search