PowerShell and SSIS

I’ve been trying to learn more and more about PowerShell and how I can use it to be a better DBA.  This morning I came across the function Get-Filehash that came out with PowerShell version 4.0 and computes the hash value for a file by using a specified hash algorithm.  I wondered if this could have helped me with a problem I had at a prior client where a network problem was sporadically causing files being copied between server to get corrupt and we wouldn’t know about it until the daily SSIS ETL process failed when it encountered the corruption.  What we needed at the time was an easy way to detect if a file changed between servers that could easily be parameterized and incorporated into the existing SSIS workflow.

I decided to break out my laptop to see if I could create a PowerShell script that could compare the Hash values of two files and be used within SSIS.   Writing a PowerShell script to check if two files have the same hash was easy.  I wrote a script that accepts as input a source file, a target file, an optional encryption algorithm, and a default verbose flag (Amateur Tip: use PowerShell’s $VerbosePreference and a parameter that defaults to false as a debugging tool).   As you can see if you look at the PowerShell script at the end of this post, I’m a big fan of verbose error messages.  Here is a screenshot of a few executions of the PowerShell script.  The first run was comparing two files that are different, the second run had an invalid file path for SrcFileNamePath, and the third run compared a file to itself.

PowerShell and SSIS Script Runs

How could I use the PowerShell script within an SSIS package and use those error messages if something goes wrong?

To call PowerShell from SSIS is just a little bit tricky.  You use an Execute Process Task as you would expect, and because that executes from the windows command line, the executable needs to be the PowerShell command, and the arguments is the command that is the name of your PowerShell Script (CompareFiles.ps1 in this case) and the parameters you wish to pass into the PowerShell Script concatenated together.  For my testing, the inputs were:

Executable:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Arguments:

-command “. ‘C:\temp\CompareFiles.ps1’ -SrcFileNamePath ‘C:\temp\Notes_20111202.txt’ -TgtFileNamePath ‘C:\temp\Notes_20111202.txt’

It’s important to note the correct use of PowerShell’s –command parameter and using the double and single quotes correctly.

PowerShell and SSIS Execute Process Task Editor 1

In order to be able to use the error messages from the PowerShell script, I used a string Variable (CompareReturnValue) in SSIS and passed the StandardOutputVariable in the Execute Process Task to it.

PowerShell and SSIS Execute Process Task Editor 2

In order for this to work, you need to make sure that your PowerShell script writes the error message to the console using Write-Host and a successful run doesn’t  write anything to the console. If you use Write-Verbose as a debugger like I do, you should know that those messages will also be passed back to SSIS, so set your $VerbosePreference to SilentlyContinue. Now you can use a Script Task and a Precedence Constraint with an expression to output the error message.

PowerShell and SSIS Package Unrun

The Precedence Constraint just checks that the CompareReturnValue is not blank to fire of an error.

@[User::CompareReturnValue] != ""

PowerShell and SSIS Precedence Constraint

The Script Task has as inputs a couple variables including the CompareReturnValue in order to display a useful error message.

PowerShell and SSIS Script Task

The script itself is very straight forward, output a few error messages and then fail the script task.

public void Main()
{

    Dts.Events.FireError(0, null,
        string.Format("{0}",
        (string)Dts.Variables["CompareFilesPath"].Value),
        null, 0);
    Dts.Events.FireError(0, null,
        string.Format("{0}",
        (string)Dts.Variables["CompareReturnValue"].Value),
        null, 0);
    Dts.TaskResult = (int)ScriptResults.Failure;

}

Here’s a successful run where I check two identical files:

PowerShell and SSIS Package Success Run

And here’s an unsuccessful run when I tried to compare a text file and a copy of the text file that I had modified slightly along with a screenshot of the relevant partition of the SSIS process tab.

PowerShell and SSIS Package Fail Run

This proof of concept package could have easily been productionalized by adding a few package parameters to it and then it could have been called by existing SSIS packages to alert when files copied across the network had become corrupt.  And it’s easy enough to add the PowerShell script to your Miscellaneous folder in your SSIS project so that the PowerShell script gets added to your Source Control solution along with the rest of your ETL code.

PowerShell and Miscellaneous Folder

PowerShell is a powerful tool, much more powerful that windows batch commands.  Using the framework described here I can see myself incorporating PowerShell into my SSIS toolkit in the future when needed.

And, finally, here is the PowerShell script I created and demonstrated: CompareFiles.ps1

				
				
				

Your email address will not be published. Required fields are marked *

Phone: 312-602-4000
Email: marketing@westmonroepartners.com
222 W. Adams
Chicago, IL 60606
Show Buttons
Share On Facebook
Share On Twitter
Share on LinkedIn
Hide Buttons