List Database connections for each SSIS package

Question

List Database connections for each SSIS package

DamianC

SSCrazy Eights

Points: 8023
More actions
August 16, 2018 at 4:32 am

#366253

Hello
Using SQL 2008 R2
We have around 300 packages all stored in one specific folder
Is there an easy way to list the databases that each package connects to?
I'm trying to establish database dependencies for each package without going into each one in turn
There seems to be a few 3rd party tools around for this
Some that look to provide excellent in-depth analysis
All look to come at a cost
I'm only after the basics in this instance
Thanks
- Damian

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Outside of loading those packages into an XML column and then parsing the XML using T-SQL, I'm figuring you would need to use VBScript or VBA to parse each file.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Lowell SSC Guru Points: 323518 More actions · Answer 2

are the packages using dtsconfig files, or do they all use embedded connection managers?

either way, you can use an SSIS package and some script tasks to scan for substrings of the dtsx/dtsconfig files.
I've done exactly that for scanning those files and rdl files too;

My technique relied heavily on letting SQL do the searches, so the package was just a data gathering device.
I stuck all the text from the files(dtsx/dtsconfig/rdl) into a SQL table, so that i could cross apply a version of DelimitedSplit on them to spit on find '<commandText' ,'Data Source= and other strings so i could find each instance of what i was looking for.

mine was just a foreach file loop that read each file into a string , inserted that string into a table, and then a few procedures post loading that parsed out that table.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DamianC SSCrazy Eights Points: 8023 More actions · Answer 3

Thanks, a package with a loop seems like a sensible option
So literally read everything in and use SQL to handle the database name extraction

- Damian