PGP inside a SSIS Package

So, you've got an SSIS package that moves data from one source, let's say an FTP site, and move it into your database. Because the data you're grabbing from the FTP is "sensitive" it's encrypted... well kinda. It's encrypted using PGP (Pretty Good Privacy). PGP is great for stuff that's pretty important, but not worth being too hardward intensive.

So Download This file

This will give you the GNU's PGP instal file.

This link will walk you through setting it up, adding needed bits to the PATH system variable, creating a key, exporting that key, and then sending the new key to someone who's going to be encyrpting data with this key.

Now that it's installed, you've got a key, and you know the steps you need to take to encrypt or decrypt the file.

Step One. Create an Execute Process Task.Drag one of these puppies onto the work area.

A double click will bring up the General Details.
A click on the Process Item on the left, below the highlighted General.
Here we find some things that are actually interesting.
It should look as follows.
The fields we're going to be looking at are as follows:
Executable - the gpg.exe executable, can be hard coded, default location is
C:\Program Files\GNU\GnuPG\gpg.exe

Arguments - this will the the command line args that you would use if you were running this
command in the DOS prompt. This will follow this general form.
--passphrase-fd 0 --decrypt-files F:\filepath\file.asc
"--passphrase-fd 0" indicates that the passphrase is going to be coming in as
an input variable.
"--decrypt-files" obviously tells us that we're going to decrypt a file at the following
location.
StandardInputVariable - this is your password. Feel free to make this a variable to allow
the password to be passed in from a config file.
FailTaskIfReturnCodeIsNotSuccess - I recomend setting this to true, it makes things slightly
easier to debug.
SuccessValue - I kinda cheat here and set this to 2. This is because the process returns a
warning. The warning is issued because gpg cannot check the signature, and because
the message was not integrity protected. (If anyone out there has any thoughts on this
I'm open to suggestions)
WindowStyle - this can stay normal or you can make it hidden, I don't know when this will
actually be seen by a person, but who knows...

Your Process section Should look as follows:
One last thing to investigate is the Expressions section. In my case the ftp file that I was downloading every day had a changingfilename such as "filename20070101". Where 1/1/2007 was today's date. This meant I needed some logic to change that filename, so I used expressions here. I also could have altered this variable from the location it was called in the code. But I chose to do everything in the SSIS package.

I defined the Arguments expression as follows:

In this case I've got an ugly variable called @FormattedDate which is defined in an abnoxous way as follows:

DATEPART("weekday", GETDATE()) == 2 ? (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -3, getdate() ) ) ) + ( DatePart("mm", DATEADD( "day", -3, getdate() ) ) < 10 ? "0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) ) : (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) ) ) + ( DatePart("dd", DATEADD( "day", -3, getdate() ) ) < 10 ? "0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) ) : (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) ) ) : (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -1, getdate() ) ) ) + (DatePart("mm", DATEADD( "day", -1, getdate() ) ) < 10 ? "0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) ) : (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) )) + ( DatePart("dd", DATEADD( "day", -1, getdate() ) ) < 10 ? "0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) ) : (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) ) )

if it's January 1st, 2007 the variable would return: 20070101 (it does...just trust me...)

At this point, we've got a ready to roll decryption task. Enjoy.