I have several packages/projects deployed in the SSISDB catalog. I want to find which packages have a specific expression attached to a variable value. Is there a TSQL way of doing this? I know the package data is encrypted in [internal].[packages]. But with the proper credentials, is it possible to decrypt it? Or what are the other options I have? Can I use some C#/Powershell script to search?
Search for a variable value in SSIS packages deployed in SSISDB integration Catalog
Viewed 258
1 Answers
There is no tables that stores this kind of information. You should search for it by reading the package XML. Using SSISDB, it is not possible to read the package XML using T-SQL since SSISDB encrypts the whole project as binary.
To read the package's XML, you should extract the project binary using the SSISDB.cataloag.get_project stored procedure. Change the extract file extension to .zip and extract its content. Then, loop over packages to check if the variable is used in each package. This can be done using C# or PowerShell:
- Extracting dtsx from Integration Service Catalog from C#
- Get Package XML from SSIS Catalog with PowerShell
In case you are storing the database within SQL Server (Msdb) you can read the package XML data from the msdb.dbo.sysssispackages table.
Related