Skip to content

Manipulating SSIS Executables with PowerShell

2014 February 10
by Shannon Lowder

Welcome back to my mini-series on Powershell, SQLPSX, and the awesome things you can do with your packages through POSH. You can catch up by reading this, this and this.  You’ve got all that, right?  Good!  Now, do you have a clone of my ManageETLDemo on your machine?  Is it up to date? Great!

In that case, let’s dive into executables as promised. Crack open the ExploringExecutables.ps1 script and follow along.  You might notice the Import-Module line has changed a little bit from my previous examples.

Import-Module SSIS -ArgumentList "2012"

This has to do with a code change I contributed to the SQLPSX project.  Originally it would assume you wanted to use the SSIS 2008 framework, unless you passed in 2005.  It didn’t support the new 2012 SSIS dll.  With the change I’ve proposed, it will look for installed versions of the dll, and allow you to call for 2005, 2008, 2008r2, 2012, or 2014 versions of the dll.  One additional trick I taught the script was if you don’t pass the ArgumentList, it will load the latest version of the dll for you!

The next few lines deal with loading the ExplorePackages.dtsx into an object we can explore through PowerShell.

$path = "C:\code\ManageETLDemo\ETLFramework\ETLFramework";
#yes, I know this is overkill to load a single package, but you can change back t0 *.dtsx and explore
#all the packages.
$packages = Get-ChildItem -path $path -Filter "ExplorePackages.dtsx";
foreach($package in $packages) {
 $packageObject = Get-ISPackage -path $package.FullName;

Now, starting from line 14, we deal with executables in our package.  Since a package can have several executables, and one executable type (Sequence containers) can contain other tasks, we use a foreach loop to spin through them.  If you run this script in PowerGUI, i’d suggest setting a break point on line 14, so you can explore the $executable object on your own.

In the script we explore the $executable.GetType() function, useful for determining if you’re dealing with a Task or a container.  Note that the data flow task reports to be a Task , rather than a container.  I’ll dig more into that object in a future article.  I have to break out some code I barely understand in order to dig into the innards of a data flow task.

I also access the $executable.Name attribute, as well as open up tasks InnerObject atributes.  In here we get access to the SqlStatementSource, the Connection, etc.  This is useful for checking your packages to make sure you’re calling your logging and auditing functions the same way every time.  It can also be useful for verifying that you are passing all the appropriate parameters, and have your result type set as it needs to be.

While you can check the $executable.HasExpressions bit, what you can’t do easily is get a list of all the expressions.  I’ll cover a trick I’ve discovered for guessing what expressions an executable has.

Every $executable has a variables collection.  If you are trying to audit all the variables in a package, you would need to also spin through each executable and look for it’s variables too.  I’ve covered that in our variables talk, but take the time and dig into that collection here and see the methods and attributes you can access from the executable.

You can also open the Properties collection, this is just about everything you can see from the SSDT GUI, when you right click and choose properties for an executable.  Having direct access from Powershell will let you verify all your packages have their properties set the same way!

Now that you’ve had a look inside the executable, I’ll move on to Event Handlers. While you could think of them as a special set of Sequence Containers you do get a few extra attributes and methods, not available for executables.  And after that, I’ll share the fundamentals of my integration testing scripts.  It’s what started this whole journey in the first place.

In the mean time, if you have any questions about SQL, POSH, or SQLPSX, ask.  I’m here to help!



PowerShell, SSIS Packages and Writing Variables

2014 January 14
by Shannon Lowder

Last time, I introduced you to SQLPSX.  Then we spun through our SSIS packages looking for a specific variable.  That’s where I started my PowerShell managing SSIS journey.  After I was able to spin through all my packages and see that some of my packages weren’t following the “standard”, I need to go into several packages and either add variables, or update the default values of those variables.

And that would take too much time to do by hand.  As a DBA, automate anything you can.  Save your time for more important things, or at least things you can’t automate yet!

Add a variable to a package

Click here to get the script we’ll use to add a variable to our packages.  The link will pop open a new tab, so you can follow along in the code with me.  In this script we add a MAXDOP variable to each package.  In one of my environments the way competition for resources between tenants is handled is by setting MAXDOP to 1 for the entire server.  The problem is, this creates less efficient query plans when we do large data moves from stage to production tables.  So, we added a parameter to our queries that we could then use from our packages and control the amount of resources used by our packages.  I know, there are better ways to handle this through diplomacy… but until those talks take place, this was our solution.  Let’s dig in.

As before, we have to import our SSIS module so we get easy access to the SSIS object.  Then, we grab all the dtsx files and load them into our $packageFileObjects.  This object holds information about the dtsx files as files, there’s nothing there we can use to do more than locate the files and see their names.

Then the script gets more useful.  For each dtsx file in that object, we’re going to create an actual $packageObject that gives us full access to the package.  Once we have that, we can then spin through each variable in the $packageObject.Variables collection looking to see if the MAXDOP variable already exists.

This first collection of variables, $packageObject.Variables, represents variables available in the package scope.  Remember, there are several scopes you will need to check in order to exhaustively search a package for a variable.  That’s why we drop down and look into all the executables in our package.

For each executable in our $packageObject, we check it’s variables collction for our MAXDOP variable.  If I knew we had containers of containers of containers, I’d have to set up some recursion here.  To be honest, I’m not that much of a programmer that’d I’d willingly show off my skills at writing a recursive function.  If you have that need, explore.  If you come to a dead end, I’ll share some of my horrible code with you.

You’ll laugh.

And then you’ll learn how to handle the recursion.

The one gotcha I found when checking my executables’ variables collection was occasionally a package scoped variable would show up in my executables list.  Unfortunately I haven’t figured out why yet.  But I did come up with a catch for that condition.  Notice the test:

if($Variable.GetPackagePath() -ne "\Package.Variables[User::MAXDOP]")

This code checks the “path” of a variable.  \Package.Variables are our package variables, and \Package.Executable[x].Variables would be a variable in one of our executables.  So this test verifies we’re just checking for executable variables at line 25.

Either way, if we find the variable, we set our $found flag to 1.  That way, if we find that a package already has a MAXDOP variable, we won’t try to add another.  That would be bad.  Try it and see.

If we didn’t find a MAXDOP variable in our package, then we add one by calling Variables.Add().  This function requires 4 parameters:

  • the name you want to give the variable
  • readonly, either $true or $false
  • namespace, User or system.  I haven’t tested what would happen if I tried to add a system variable to a package.  Give it a shot and let me know what you find.
  • value — what value do you want to store there by default?

After you add the variable, you have to save the file, otherwise you didn’t do anything.  We call the opposite of Get-ISPackage, Set-ISPackage, pass it the filename, and the -Force parameter, so it overwrites the file, and we’re done!

While exploring this POSH script, set a breakpoint on line 14, and explore the functions and attributes you have access to on the variable.  You’ll find you can set the .Value, .Expression, and more attributes of the variable, just like you can through the BIDS or SSDT development environment.  It’s just that this time, you can automate those steps, and execute them across many packages.

I’ve used variations of this script to add variables to hundreds of packages in minutes.  I’ve also been able to find packages that had the wrong default values, update those values, and save the packages again…in minutes.  Using POSH to automate many of the tasks that are time intensive just makes sense.

Next time, Let’s look into some of the things we can do with executables. Let’s look at some of the gotchas, and see how much more time we could save by dealing with executables through PowerShell, rather than the GUI.

If you have any questions, send them in.  I’m here to help!



PowerShell Management for Your SSIS Packages

2013 December 10
by Shannon Lowder

In addition to working on my database sharding presentation I’ve been working on upgrading an ETL framework.  In learning this new framework I dug into the business logic as to what the framework was trying to load, looked into all the auditing that was currently captured, and I looked for gaps when packages didn’t perform as expected.  It took a few weeks to dig through the full project, and now that I’m starting to make incremental improvements to this process, I’m finding PowerShell can be a powerful tool in your SSIS tool belt.  The trick is, you have to sharpen that tool before you can really use it.


SSIS Packages are just XML right?  So you can just parse out the XML and interact that way, right?

You could.  It would take a lot of time and energy, but you could.  I’m fortunate to have stumbled across SQLPSX, SQL Server PowerShell Extensions on codeplex.  With this, I was able to really dig into my packages and manage my variables (both at the package and container level), connection strings, executables, and more!  All of that without once having to build out a XMLPath statement or build out a complex TRY..CATCH to see if the package put the variable in the root node, or in one of the children nodes of the current element.

This tool hasn’t been touched since March 2011, but it’s served as the base for several tools I built over the past week.  Download it, and PowerGUI, my current PowerShell IDE.  (If you know a better IDE for PowerShell, hit me up in the comments!)  Then get started with a couple of these demos.

What packages have this variable?

In this framework we moved from building MERGE statements through expressions, to building them via a stored procedure.  The problem is we have nearly a hundred packages I have to go check to find out which ones were already upgraded, and which ones haven’t yet been upgraded.

How much time would that take to open each package in your project, look for the variables and build a to-do list?

Too much.

Let’s do it the easy way:

update: I’ve added the management scripts to a GitHub repository, and you can find the reading variables script here!

Import-Module -Name c:\code\posh\SQLPSX-Modules\SSIS
$path = "C:\code\localrepolocation"
$loadPackages = Get-ChildItem -path $path -Filter "*_load.dtsx"
foreach($loadPackage in  $loadPackages) {
     $packagePath = $path + "" + $loadPackage
     $package = Get-ISPackage -path $packagePath
     foreach($variable in $package.Variables) {
          if ($variable.Name -eq "VariableName") {
	       $package.Name + " has VariableName, with value: " + $variable.Value

Ok, let’s start at the top.  The first line gives me access to the SSIS POSH modules.  Since I will be deploying this to other machines, I will have to make sure I deploy both this script, and the SSIS module to the same folder structure as I use in dev.  If I don’t then I would need to update my package to new paths.

Next, I set a variable for the path where I’ve stored my packages.  This is the working clone of the ETL repository.

Then, I spin through and grab all the packages in that folder that end with _load.dtsx.  The up-side to having a naming convention is I can spin through my packages and trust that I’m grabbing only the load packages, and not my stage, transform, or transfer packages.  The -Filter option can work wonders with the Get-ChildItem commandlet.

Once I’ve loaded all my packages into the $loadPackages object, in this case I think it’s a collection).  I use a foreach to deal with each package one at a time.

CaptureI build out a fully qualified name and path for each package, which I need to pass on to the Get-ISPackage commandlet from SQLPSX.  This is the real engine in this tool.  It grabs your package, and then loads it into an object that IS your package!

Once you have this object, you can do nearly anything you could do through BIDS/SSDT.  Check out the screen shot, then try it for yourself.  Explore the object, see what all you have access to. It will blow you away once you see it all laid out right in front of you!

Now, back to the script.  The next foreach loop spins through all our package variables, and checks their names.  If we find the one named “VariableName”, then we output “That package has VariableName, with value: blah”.  That output becomes your to-do list.  You can then go and perform the work of upgrading just those packages that need to be upgraded, without having to open those that don’t have any work to-do!

Think about the possibilities of looking at all of your packages, and seeing all the variables you are using.  You could create a CSV of all the package names and variable names.  You could then drop that into a database, and build a query that would show you which packages aren’t using your standard variable names.  You could also check for variables with the wrong default values.  The key is this: anything you can do through the GUI, you could now do through PowerShell.  That means it’s automatable, repeatable, and reduces the chance for human failure.

In a future article, I want to share with you how I built a testing framework that spins through just the files I have modified and not checked in to source control, executes them, checks to see if they succeed or not.  If they succeed, then it goes to compare the results in the database versus the last production run, if the results are the same, then the output is validated.  I could then build on top of that to make that a requirement before checking my new or updated packages into the repository!  This is test-based development for SSIS folks!

This is exciting stuff folks!

As always, if you have any questions, please send them in.  I’m here to help!