Locate MSDB's SSIS package path for Microsoft-Created proc

Question

Locate MSDB's SSIS package path for Microsoft-Created proc

Brandie Tarvin

SSC Guru

Points: 173105
More actions
January 20, 2017 at 7:09 am

#334306

Microsoft has a wonderful (I assume) proc for pulling connection strings from the XML of an SSIS package. All one needs to do is supply the SSIS package path and run the proc. The problem is, I can't figure out how to get the location of my SSIS packages.
Yes, I've looked in msdb.dbo.sysssispackages and msdb.dbo.sysssispackagefolders. I've tried using the Folder name in the later for the proc, it doesn't work. I've tried using "\MSDB\FolderName", it doesn't work. This is the error I keep getting.
Operating system error code 3(The system cannot find the path specified.).
I want to search multiple packages without having to put in each one separately, but I will be happy if I can just figure out how to pull the path of just one. Any thoughts?
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

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

Phil Parkin SSC Guru Points: 247158 More actions · Answer 1

Brandie Tarvin - Friday, January 20, 2017 7:09 AM
Microsoft has a wonderful (I assume) proc for pulling connection strings from the XML of an SSIS package. All one needs to do is supply the SSIS package path and run the proc. The problem is, I can't figure out how to get the location of my SSIS packages.
Yes, I've looked in msdb.dbo.sysssispackages and msdb.dbo.sysssispackagefolders. I've tried using the Folder name in the later for the proc, it doesn't work. I've tried using "\MSDB\FolderName", it doesn't work. This is the error I keep getting.
Operating system error code 3(The system cannot find the path specified.).

I want to search multiple packages without having to put in each one separately, but I will be happy if I can just figure out how to pull the path of just one. Any thoughts?

Are they deployed to SSISDB?

Brandie Tarvin SSC Guru Points: 173105 More actions · Answer 2

No. MSDB.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 173105 More actions · Answer 3

Okay, no one has replied. I'm going down a different path. Let me know if anyone has an answer to this, because I would love to find it.

BTW, the package source is "SQL Server" which, to my knowledge stores SSIS packages straight to MSDB.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Locate MSDB's SSIS package path for Microsoft-Created proc

Cookies on SQLServerCentral