Archive for SSIS

Using GetPackagesInfos2

January 31, 2012 Coding, SSIS No comments

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.

Book chapters complete

January 23, 2012 imawesome, SSIS No comments

My Book

I was lucky enough to be invited to write some chapters for the upcoming Professional SQL Server Integration services book. This was a very unique experience unlike anything I’ve ever done before. I’ve never had someone really judge my writing skills (or lack thereof) before. So to have someone judge almost every sentence I wrote was very weird. I’m sort of glad that I can get away with writing code that doesn’t have to be judged line by line (well, except code reviews). That said, I had fun doing it and now I get to put that on my resume. Having a book published by Wrox is a pretty significant career and resume booster.

My chapters in the book cover the programming side of SSIS in terms of writing custom components. In essence they will be the chapters that most BI developers skip right over but programmers will read. Not many BI Developers I know are all that interested in writing custom components. They prefer to write script tasks and components to do what they need. I have to agree with them for the most part but the problem with script tasks and components is the lack of re-usability. If they need to use the same code base again they basically have to copy and paste it over and over again. Which sucks. So my chapters cover the pain staking task of creating custom components that can be used in any ssis package they create without copy and paste. Yes, it is quite a pain to create them. I do it every day writing Task Factory.

While I liked the experience of writing this book it’s one of those things you have to try to see if you like it for yourself. It was never fun having the Technical Editor remind me that I forgot essential parts of SSIS in 2012 and having to go spend hours / days learning new undocumented classes. Anyone who’s ever had to cry their way through writing code with undocumented Microsoft classes knows the pain I was in.

Was it worth it? Absolutely! While I won’t be spending time in France having Gertrude Stein review my work, I’m pretty proud of this accomplishment. I do, however, look forward to spending time in a local starbucks. Maybe at some point, when I visit Charleston, with my buddy Patrick Riddle.