extracting SSIS package source code from SSISDB Catalog deployed projects using TSQL

Question

I need to search all the SSIS Projects deployed on a server SSISDB Catalog for a specific substring.

My idea is to write a SQL Query on SSISDB.catalog views and internal.package tables to access the package_data field, which is of type varbinary(max) and I was thinking it might contain the package source code, but I found out that this column is always null.

Using SSMS it's possible to export the ispac files for deployed projects, therefore the package content it's hidden somewhere.

So the question is: how can i extract the ssis pacakges source code using TSQL?

this query retuns no rows on my SQL Server 2022 instance

SELECT TOP (1000) *
 FROM [SSISDB].[internal].[packages]
 where package_data is not null

• J.D.: Why do you need to do this in T-SQL / what's the end goal? I imagine the package is compiled, making this not directly possible.
• ImperviousInclemency: Any chance you are looking for strings in environment variables or project/package parameters, or could the substring be anywhere (including a component name or a comment on the canvas)?

Answer 1

I'm not sure if you can do anything with TSQL, but if you're trying to find a string as it occurs in an integration, and don't have access to the original project, you can use Visual Studio with Integration Services extension.

1. Create a new project of type Integration Services Import Project Wizard.
2. In the wizard, either use your .ispac file you referenced, or connect to your SQL Server with the integration services catalog
3. With your integration files now loaded into the project and saved, you can navigate to the project folder and find the .dtsx files, open them in your desired editor and search to your heart's content
4. You may even be able to upload their text into an XML column in SQL should you want to search using TSQL, though I think you'd find it easier to navigate XML using other tools

• sergiom: Thank you, but the problem is that there are hundreds of SSIS projects. That search I need to make is precisely to find the projects containing the packages with that string.