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!

 

 

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!