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!