Recently I was creating the Advanced Execute Package task for Task Factory. One of the ways to retrieve a list of packages from SSIS is by using the GetPackageInfos method. However, the problem with GetPackageInfos is that it requires you to pass in a username and password. With the use of an OLEDB or ADO SSIS connection manager, this isn’t possible. You can grab the username from the SSIS connection manager connection string but you cannot retrieve the password. In comes GetPackageInfos2 which is an undocumented (of course it is!) way of retrieving packages from the MSDB server. The function signature for GetPackageInfos2 is:
Public Function GetPackageInfos2 ( _ strFolder As String, _ connectionObject As Object _ ) As PackageInfos
Notice the second parameter is an Object type? Yeah, uh… good luck finding out what it is on your own. Fortunately I figured it out and was able to use it in my project… and so that you can just read this blog and not waste a few hours looking for an answer.
Turns out the connectionObject parameter is looking for an SSIS connection manager object. So to get the GetPackageInfos2 to work you simply grab a reference to a connection manager and pass the connection manager object into the function. mConnections is an a Microsoft.SqlServer.Dts.Runtime.Connections object.
Dim app As New Microsoft.SqlServer.Dts.Runtime.Application Dim cm As Microsoft.SqlServer.Dts.Runtime.ConnectionManager = mConnections("MyConnectionManager") ' pass in the root folder of "/" Dim packages As Microsoft.SqlServer.Dts.Runtime.PackageInfos = app.GetPackageInfos2("/", cm) For Each package as PackageInfo in packages Console.Writeline(package.Name) Next
So it’s as simple as that. Grab the connection manager reference from an OLEDB or ADO.NET connection managers, pass it into GetPackageInfos2 and you got your reference.
Leave a comment if you have any questions.