Search ALL SSIS Packages for a string

SQL Server Administration
1

Hi experts,
Not much activity in the SSIS forum so I will post here.
I need to search all SSIS packages for a certain string. It's the IP address of a linked server.
The packages are SQL 2019 and they are stored in the SSISDB database.
thanks for any ideas.

2

I can't remember if packages are stored in a searchable format in SSISDB, it may be that they're just binary-encoded XML. I don't have the database schema handy to check.

In the past I've used dtutil to extract SSIS packages to XML files and searched those:

It's a bit more work that you'd probably want to do, but it's not that difficult to script it to extract all the packages to files. Once that's done, use your favorite file search utility (find/findstr/grep, etc) to search the XML files. Ensure that you don't encrypt the package files entirely, either no encryption or just sensitive data.

1 Like
3

Thank you, @robert_volk

4

I just went through the dtutil documentation, and I don’t see any way to extract a package from the SSISDB to an XML file.

If it can in fact be done, could you please share which command option enables it?

Thanks!

5

You can modify the copy example:

And put the /DTS parameter before /COPY to make SSISDB the source, and then use /FILE as a destination. The file output will be XML, the .DTSX extension is just the convention for SSIS package files.

Edit: the link I posted may not navigate to the section I intended, search for “Copy examples” and you’ll find the right part.