Tech Talk A software developer on all things tech, and then some

5Feb/140

GnuPG Encryption / Decryption in SSIS 2012

GnuPG (GNU Privacy Guard) is a free implementation of the OpenPGP standard as defined by RFC4880, not to be confused with the proprietary PGP (Pretty Good Privacy) version originally created by Phil Zimmermann. When needing a quick, easy, and free solution to encrypt and decrypt OpenPGP standard files in a SSIS package, GnuPG was the way to go.

Note to Advanced Users : All I am pretty much doing is creating a Execute Process Task, pointing to the GnuPG executable and passing some dynamic cmd args. If you are unaware of this process, read on!

Encrypting Files

First, create a new Integration Services project and select the default package which is created. Then drag and drop an Execute Process Task to the Control Flow and name it something unique so you can remember later on which task will encrypt and decrypt.

Next create a string variable, name it something similar to cmdArgs, and view the Properties [Alt + Enter]. Find the key, EvaluateAsExpression and set it to true. Select the key Expression, and select the ellipse that appear next to the value.

Next we are going to need the following values :

  • HomeDir - Where your GnuPG files are located (needed for keyrings and such)
  • OutputLocationName - The full path to the output file with the PGP or GPG extension.
  • KeyName - Which key to use to encrypt the file
  • SourcefileLocationName -  The full path to the source file with the PGP or GPG extension

In the expression builder which just appeared, create the following expression (Replacing the keywords above with your values or SSIS Variables)

"--homedir " + @[User::HomeDir] + " --output " + @[User::OutputLocationName] + ".pgp -r \"" + @[User::KeyName] +"\" --encrypt "  +  @[User::SourcefileLocationName]

Double click the new Execute Process Task and click Process. Under executable, type the path in which your executable resides. (Network locations will also work). Next click Expressions, then click the ellipsis next to expressions. Under property select Arguments, then under Expression set it to the cmdArgs variable which you created above. I kept the rest of the defaults except TimeOut, which I set to 60 vs 0.

There you have it, a task to encrypt files in SSIS using GnuPG!

Decrypting Files

We already have created a project above, so to move onto decrypting, drag and drop a new Execute Process Task. Next create a string variable similar to the above cmdArgs, and view the Properties [Alt + Enter]. Find the key, EvaluateAsExpression and set it to true. Select the key Expression, and select the ellipse that appear next to the value.

Next we are going to need the following values :

  • HomeDir - Where your GnuPG files are located (needed for keyrings and such)
  • OutputLocationName - The full path to the output file with the PGP or GPG extension.
  • SourcefileLocationName -  The full path to the source file with the PGP or GPG extension

In the expression builder which just appeared, create the following expression (Replacing the keywords above with your values or SSIS Variables)

"--homedir " + @[User::HomeDir] + " --output " + @[User::OutputLocationName] + " --decrypt " + @[User::SourcefileLocationName]

Double click the new Execute Process Task and click Process. Under executable, type the path in which your executable resides. (Network locations will also work). Next click Expressions, then click the ellipsis next to expressions. Under property select Arguments, then under Expression set it to the cmdArgs variable which you created above. I kept the rest of the defaults except TimeOut, which I set to 60 vs 0.

That's all there is to it when creating a ssis task to encrypt and decrypt using GnuPG! If you have any questions / comments about this article, or suggestions about other articles, please either contact me or reply below! Thanks all and happy coding!

Comments (0) Trackbacks (0)

No comments yet.


Leave a Reply

No trackbacks yet.

%d bloggers like this: