How to Decrypt Files with PGP using SSIS
-
I was able to create code using Script Task to receive files from FTP site and then delete them, now I need to decrypt these files I brought over but I don't know how to start or what Control Flow Item to use.
I have PGP 10.0.0, SQL Server 2005
Thanks!
-
[font="Comic Sans MS"]
Script Task or Execute Command task.
See here .
[/font]
[font="Comic Sans MS"]--
Sabya[/font] -
I wasn't able to use anything from this link, I don't know how to put it back to use in Execute Command or Script Task. I copied the code at the bottom but it had a lot of errors in the Script Task.
-
You need to get a PGP product that has command line capabilities. Then, use an Execute Process task to envoke a command shell and run the decrypt process in DOS.
I had to do this for a client a while back. I used DTS (they were not on SQL Server 2005 yet). The PGP product they had did not have command line capabilities so I had to find a freeware product that did. I used this[/url] product. It was pretty easy to use and their knowledge base and forums helped me through a bunch.
-
So I can download this and then would I use Execute Process Task instead of Script Task for SSIS?
Do you have an example of what I would enter for the lines?
Thanks!
-
smacumber 77733 (6/25/2010)
So I can download this and then would I use Execute Process Task instead of Script Task for SSIS?Yes.
Do you have an example of what I would enter for the lines?
Unfortunately, I worked on this project a few years ago and I no longer have access to the system where I could retrieve my work. I figured out everything I needed to write the decrypt code from the examples and forum threads on their web site.
-
Just a hint, you may need to use SSIS to build out the DOS command string, stream it to a file, and then use the Execute Process task to envoke DOS and run the file.
-
smacumber 77733 (6/25/2010)
I was able to create code using Script Task to receive files from FTP site and then delete them, now I need to decrypt these files I brought over but I don't know how to start or what Control Flow Item to use.I have PGP 10.0.0, SQL Server 2005
Thanks!
If you can use third-party solutions, check the commercial CozyRoc OpenPGP Task. It is completely integrated in SSIS and part of SSIS+ library.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
We are not interrested in purchasing a third party solution just to do one thing.
Thanks though.
-
John Rowan (6/25/2010)
Just a hint, you may need to use SSIS to build out the DOS command string, stream it to a file, and then use the Execute Process task to envoke DOS and run the file.Unfortunetly I have no idea what I am doing with the command line.
Don't know what to enter for the arguements, I currently have:
--passphrase- 2 --decrypt-files C:\PGP Files\file.csv.pgp
This is a test file that doesn't require a key.
And it doesn't work
I downloaded GNU so for the Executable I have:
C:\Program Files\GNU\GnuPG\gpg.exe
and Working Directory:
C:\Program Files\GNU\GnuPG
Sorry really lost on this one.
-
You are awesome!
Thanks!!
😀
-
Try something like this in your Execute Process task:
Executable: [full path to your .exe]
Args: [set this as an expression to a variable]
Working Dir: [working dir of your .exe]
Create variables (can be expressions and/or from configs) for:
Password
ExePath
Output file name
Input file name
Finally, create a variable that's built with an expression that is along the lines of (copy/paste this, keeping the quotes):
"cmd /c \"echo " + @[User::Password] + "| @[User::ExePath] --passphrase-fd 0 --output \"" +@[User::OutFile] + "\" --decrypt \"" + @[User::InFile] + "\"\""
I use this and it works quite well ...
-
(too clarify, that last expression in my response is for the Args variable to be used in the Exec Process task)
-
dg227 is exactly correct in that you'll want to use variables to dynamically configure a task.
With that said, you still need to get the syntax down for your decrypt command. I would suggest playing around with the command line outside of SSIS just to get your comfort level up with the pgp product.
So, if you enter the following command at the dos prompt, what happens:
C:\program files\gnu\gpg --passphrase 2 -d C:\PGP Files\file.csv.pgp
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply