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?
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:
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.
