Adventures in Powershell SSIS Administration Programming

In January 2009, I released version 1.4 of SQL Server Powershell Extensions which included a Library of functions for working with SQL Server Integration Services (SSIS). This post describes the functionality included in the script library including enumerating msdb package stores, getting packages from msdb and file stores as well as copying packages and folders among other things. At the time I felt the script library was just a way to prototype what should later be implemented as a Powershell Provider. I even named the functions after what I would later implement in a provider (Get-ISItem, Copy-ISItem, New-ISItem, Remove-ISItem, Rename-ISItem, Test-ISPath) prefixing eaching with IS for Integration Services.
Over the past week I’ve been thinking about how to code an SSIS Powershell provider by re-reading Professional Windows Powershell Programming* chapter on Providers, re-familiarizing myself with the SSIS Powershell code and pouring over the Microsoft.SqlServer.Dts.Runtime Namespace (ManagedDts) documentation. I haven’t writen a line C# code for my SSIS provider project, yet I’ve learned a few things…
  • Writing a provider is hard or at least harder than writing a cmdlet. Haven written a few cmdlets myself, its really not that much more code than writing a classic console (exe) type application. If you can write a console application, you can write a cmdlet.
  • I have a new found respect for those who have written providers including the SQL Server 2008 provider. This is some advanced programming stuff and requires a lot more thought than writing a cmdlet. Although I feel a few dozen cmdlets for SQL Server is more useful than a provider interface, my hat is off to you, Michiel Wories, for creating the SQL Server 2008 Powershell provider.
  • The more I use ManagedDts, the more I appreciate SQL Server Management Server (SMO).  SMO is elegant, well-laid out and after a bit of a learning curve generally makes sense. ManagedDts on the other hand is missing core functionality which is exposed through Microsoft’s own SQL Server Management Studio (SSMS), Dtexec or BIDS. As someone who automate things through scripts, I find the limited support for SSIS administration scripting disappointing.
I don’t want to turn this post into a rant, so instead of listing every SSIS administration and scripting issue of which there are many, I will illustrate just the top one that is preventing me from creating an SSIS Powershell provider. First, let’s go over the setup I have. I’m using SQL Server 2005 Standard Edition named instance called Z002SQLEXPRESS. I know the name says SQLEXPRESS, but its not I just don’t feel like changing demonstrate code and naming the instance other than SQLEXPRESS. Because I use a named instance I modified the MsDtsSrvr.ini.xml file located in C:Program FilesMicrosoft SQL Server90DTSBinn directory according to the SQL Server documentation and added the following entry:
    <Folder xsi:type="SqlServerFolder">
I then created a SSIS package called "test" and deployed to the SQL Server store. I can the connect to Integration Services through SSMS 2005 as shown the in following screen shots:
Now let’s try to connect to Integration Services using Powershell and enumerate the folders and packages.
First load the ManagedDTS assembly (note this should be a single line). I’m loading the 2005 instead of 2008 assembly.
[reflection.assembly]::Load("Microsoft.SqlServer.ManagedDTS, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91") > $null
To load the 2008 assembly:
[reflection.assembly]::Load("Microsoft.SqlServer.ManagedDTS, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91") > $null
An Application is the main class in the ManagedDTS, most of what an administrator would be interested in is accessible via its methods. So, next we’ll create an SSIS Application object:
$app =  new-object ("Microsoft.SqlServer.Dts.Runtime.Application")


And finally we will enumerate the folders and packages using the GetPackgeInfos method.


The following information is returned:


Problem #1: The call to GetPackgeInfos requires the SQL instance name i.e. Z002SQLExpress while the equivalent SSMS SSIS connection you specify just the server name i.e. Z002

Next let’s load an SSIS package called test stored on the root of the package store. Note this does not execute the package this just loads the package into a variable, you can then execute using the execute method.

$package = $app.LoadFromDtsServer("Z002_SQLEXPRESStest",‘Z002’,$null)

Problem #2: The call to LoadFromDtsServer requires the top level folder as defined in MsDtsSrvr.ini.xml while the equivalent SSMS SSIS Object Explorer just presents it. Furthermore there are no methods in ManagedDts to return the top level folder. So, basically we have GUI, SSMS that includes functionality not available in the API one would use to automate SSIS administration

Here’s what I’d like to be able to do in an SSIS provider, but can’t because the API does not expose the needed functionality:

cd SSIS:


cd Z002

Get top level folder and the

SSIS:Z002> get-childitem

There are still some useful things to scripting SSIS administration activities. I recently worked with a DBA to move dozens of SSIS packages and the folder structure from one SQL Server to another. By using a Powershell script over a GUI approach this saved him a few hours of work. Here’s an example of the script we used which is part of the LibrarySSIS functions in SQLPSX:

copy-isitemsqltosql -path  -topLevelFolder ‘Z002_SQL1’ -serverName ‘Z002SQL1’ -destination ‘Z003_SQL2’ -destinationServer ‘Z003’ -recurse $true

I created a Connect item requesting functionality in Microsoft.SqlServer.Dts.Runtime to support discovering the top level folder and using the server name instead of the instance name. Both of these items are already in SSMS. Please vote on my connect item.

Related Posts:

 *Note: Professional Windows Powershell Programming is a book geared for developers to create C# Powershell cmdlets and Providers. This is the best book on the subject. It’s also the only book on creating snapins, cmdlets and providers. Because of the topic of the book I only recommend it to those who want to write C# cmdlets and providers. Most Powershell users will not have a need to do so and instead will simply use the built-in or 3rd party cmdlets and providers.

This entry was posted in PowerShell. Bookmark the permalink.