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!