Hi, is there are way using sql that brings a list of the ssis packages deployed along with the details of the connections used.
There are approx 100 packages and to manually open is not realistic.
So I'm interested to know which packages/dtsx are using a OLE DB connection and which database name it's connecting to.
Thank you
If you are using the SSISDB then you can query internal.packages and do some validation on that in the package_data column which you would have to do some manipulation against to get the XML out and then shred that to get the connection manager details.
But realistically you should have all your solutions/projects in a source control environment where you can easily do a search in the source code for all your connection manager strings as it would be like you are doing from the deployed internal.packages table in the catalog.
February 3, 2023 at 11:21 am
Tried the SSISDB cataog, the package_data column shows all null values for some reason.
However, we do have source control (GitLab), so I can search in there.
Thank you
February 3, 2023 at 1:19 pm
Searching the dtsx files in source control is your best best. The package XML is encrypted in SSISDB, do don't waste your time on that.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 3, 2023 at 1:23 pm
I wrote an article a while ago which may be of interest. It shows how to decompose the DTSX XML into something usable.
https://www.sqlservercentral.com/articles/ssis-variable-and-parameter-analysis
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply