CODEWITHSUNDEEP
sqlsql-servert-sqlssisetl
need_the_buzz
need_the_buzz

Reputation: 373

Search for a variable value in SSIS packages deployed in SSISDB integration Catalog

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?

Upvotes: 2

Views: 1120

Answers (1)

Hadi
Hadi

Reputation: 37348

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:

In case you are storing the database within SQL Server (Msdb) you can read the package XML data from the msdb.dbo.sysssispackages table.

Upvotes: 1

Related Questions