October 27, 2008 at 9:54 am
Anyone out there ever had a need to encrypt files using SSIS?
I have tried changing the arguments execte sql task to be
gpg --recipient "XXX" --output "C:\Testing.zip.gpg" --encrypt"C:\Testing.zip"
It executes,no erors but no gpg file created.The same syntax works in command line and file gets created.
If there are any other ways of doing this.Do advise.Thank You!
October 27, 2008 at 10:24 am
Please ensure that SSIS runs under the same user as the one who has the keyring declared for.
In order to do this in a relatively clean way, you'll have to create a local account for the agent, migrate the private keys there, and use this account for the SQL Server Agent service.
October 27, 2008 at 10:39 am
Hi There,
Here is the current setup.
1) Installed GPG under my account
2) Test execution in command prompt with my account
3) SSIS package created by Me
4) Package excution done locally on my machine in debug mode.
Any idea if the arguments are being passed correctly ?
October 27, 2008 at 1:09 pm
Changing to this argument and including some space,got it to work
--recipient "XXX" --output "C:\Testing.zip.gpg" --encrypt"C:\Testing.zip".
How can I pass the file names as variable's.I have 2 files to be processed with following format
A_20081027.zip & B_20081027.zip
October 27, 2008 at 1:21 pm
LOOKUP_BI (10/27/2008)
Changing to this argument and including some space,got it to work--recipient "XXX" --output "C:\Testing.zip.gpg" --encrypt"C:\Testing.zip".
How can I pass the file names as variable's.I have 2 files to be processed with following format
A_20081027.zip & B_20081027.zip
You can use an expression for the argument (see properties, expressions).
To set up the filenames, you can calculate the date in another expression or whatever logic you need, before.
October 27, 2008 at 2:44 pm
After some playing around now I understand the logic of Expressions.
I got it to work.Thank You
October 28, 2008 at 8:48 am
I've got encryption working OK but now I am having an issue with decryption. Is there a way to send the passphrase to the executable? Using the execute process fails because I can call the gpg executable with the decryption option but it is waiting for the passphrase to be entered and I can't find an option to send the passphrase along with the other parameters.
TIA,
Joe B
October 28, 2008 at 8:56 am
Hi There,
I nvr did any Decryption with PGP in SSIS.
Im guessing you can pass the passphrase as an Input Variable to Execute Process Task.
This site might help you
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3978602&SiteID=1
October 28, 2008 at 10:05 pm
The decryption process with gpg and ssis is on this site:
http://codingstuffs.blogspot.com/2007/03/pgp-inside-ssis-package.html
December 21, 2008 at 9:21 am
Also check PGP task for SSIS
December 22, 2008 at 1:03 pm
I use the following in SSIS "Execute Process Task" and it works perfectly for me.
1. Set the Executable as "CMD.EXE"
2. Set the Argumetns under the Expression as follows:
"/C D:\\GnuPG\\gpg --encrypt -r [Your PGPKey] --armor < " + @[User::vOutputPath] + @[User::vFileName] + " -o " + @[User::vOutputPath] + @[User::vFileName] + ".pgp" + " --always-trust --yes"
Note: D:\GnuPG\gpg is the path where the PGP tool is installed. You can change it to the path where you have currently installed the PGP tool.
[Your PGPKey] - PGP key used to encrypt the files.
@[User::vFileName] - SSIS Variable that stores the FileName
@[User::vOutputPath] - SSIS Variable that stores the Path for the Filename.
-Amol
Amol Naik
September 11, 2009 at 10:11 am
Tim,
could you please help me how can I encrypt a file in PGP using SSIS?
I have a key by using which I have to encrypt,after the encryption I have to FTP the file and at destination by using the same key they will decrypt it. I have 'PGPDesktopWin32-9.10.0' installed on my machine.
I am unable to do it thru command prompt,but able to encrypt manually.
Please suggets me how can I encrypt using SSIS(If I could use Execute process task,what are the parameters I have to give?)
Thanks,
Vishnu
September 11, 2009 at 12:14 pm
Vishnu, the critical thing is to get the command prompt options working properly before you wrap it into SSIS. You'll have to check your PGP software's documentation to find out which command options to use, and these will vary depending on the vendor and version.
Just curious, what is your PGP software?
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
June 16, 2010 at 10:56 am
I've reviewed the blog post actually used that as a reference when setting this up.
My issue is that I can run this interactively in BIDS and it works fine. When I run it on my local server, that is the same laptop that I use when running in BIDS, it does not work. I set the error value to "2" like in the blog and it does not fail but does not encrypt the file. when set to 0 I get a fail with the message:
Running win 7 64 bit. sql 2005 standard sp3
Message
Executed as user: my AD Acct. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:27:51 AM Error: 2010-06-16 11:27:51.70 Code: 0xC0029151 Source: Cmd Shell call GPG program to encrypt file Execute Process Task Description: In Executing "C:\Program Files (x86)\GNU\GnuPG\gpg.exe" "--always-trust --yes --armor --recipient xxxxxxxxx --output C:\VS_Working\SSIS\xxxxxx.txt.pgp --encrypt C:\VS_Working\SSIS\xxxxxxxx.txt" at "C:\Program Files (x86)\GNU\GnuPG", The process exit code was "2" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:27:51 AM Finished: 11:27:51 AM Elapsed: 0.437 seconds. The package execution failed. The step failed.
now why would this not do the exact same thing when running as a SQL Job? Also I have the job running under a proxy that is setup with my AD account. Is there anyway to see what is happening or get more error info? I did find a post about calling cmd.exe and pass in the executable and arguments as an argument for cmd.exe. Again works in bids not as a job.
anything would be helpful, out of options.....
June 16, 2010 at 11:50 am
What do you mean by proxy? In my case, I had to log to the machine with the sql agent service account and import the keyring.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply