3

I need to query the SSISDB to view the configuration information of each package. I can easily view the package names and their folders but am unable to query the XML behind it and more importantly the Config information.

Has anyone queried this information before? Is it in the database or secreted away in a config file somewhere?

Hadi
  • 36,233
  • 13
  • 65
  • 124
PseudoToad
  • 1,504
  • 1
  • 16
  • 34

2 Answers2

2

SSISDB doesn't contains any table or view that allows to query the packages XML.

You need to extract the .ispac file from SSISDB using powershell or .NET and unzip the file to obtain the .dtsx files

While searching i found 2 similar topic they can gives you some insights:

Hadi
  • 36,233
  • 13
  • 65
  • 124
1

I don't think this is the level of detail you're looking for, but I figured I'd mention it...

If you're using the Project Deployment Model there are views you can query in SSISDB/Views under the catalog schema. This includes projects, packages, environments, environment variables, etc. It's not at the XML level, but I thought I'd mention it in case that covers the config info you're looking for.

bottomsnap
  • 221
  • 2
  • 5
  • Unfortunately, this is not the level of detail I needed. In the end, I wrote a PowerShell script using the SSIS library and exported all of the packages and unzipped them out to my local drive. From there, I opened them all in a text editor and did a global search. Definitely wasn't pretty but it did the trick! – PseudoToad Sep 14 '19 at 15:30