February 21, 2013 at 10:20 am
I developed SSIS Package with Execute Process Task which encrypt the text file into PGP encrypted file.
For PGP encryption with Public Key I processed following steps:
Installed gnupg software for gpg encryption.
Import the public key using command :
C:\Program Files (x86)\GNU\GnuPG\gpg --import test.asc(public key file).
Giving trust to ultimate
Setup following properties in Execute Process Task's Process Properties :
--Executable : C:\Program Files (x86)\GNU\GnuPG\gpg.exe
--Arguments : "--recipient KeyRing --armor --output "+ @[User::FileOutDir] + @[User::EncryptedFileName] +" --encrypt "+ @[User::FullFilePath]
Where -- KeyRing is user id of the Public Key
This works fine when I run the package from Bids and created encrypted file as desired.
But when I tried to run this SSIS package from SQL Server Job, It gave the following error:
Source: Execute Process Task Execute Process Task Description: In Executing "C:\Program Files (x86)\GNU\GnuPG\gpg.exe" "--recipient KeyRing --armor --output D:\SSIS_EXPORT_IMPORT_FILES\\Test_Export_SSIS\File_Out\Test_File_130221094201.gpg --encrypt D:\SSIS_EXPORT_IMPORT_FILES\Test_Export_SSIS\File_Out_BackUp\Test_File_130221094201.txt" at "D:\SSIS_EXPORT_IMPORT_FILES\Test_Export_SSIS\File_Out", The process exit code was "2" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:41:59 PM Finished: 9:42:02 PM Elapsed: 3.167 seconds. The package execution failed. The step failed.
Can anyone give me suggestion how can I perform the task successfully in SQL Server Job??
February 21, 2013 at 11:43 am
1. Make sure gpg is correctly installed on the server in the location you're referencing.
2. Log into the server as the SQL Agent service account and accept the key as 'trusted' from the command line, i.e. run the command line the SSIS package tried to run and then answer any interactive prompts gpg presents.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 21, 2013 at 8:39 pm
thanks you for the post.
As the package successfully execute and create encrypted file when runs from BIDS, the gpg installation and path is correct.
Only the problem is it gives error when scheduled to run the package from SQL Job.
February 21, 2013 at 9:10 pm
The successvalu property foe execute process task is configurable. Even though the default is 0, you should change this according to the process you are using.
You should refer the manual for your application to see the expected exit code for succes and configure that in your Execute process task
February 21, 2013 at 9:26 pm
Are you saying that you are developing in BIDS on the same machine where SQL Server Agent is running? What account is the SQL Agent service running as? Log into the machine as that account and run the gpg command line to ensure the account can use gpg properly.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 21, 2013 at 9:54 pm
Thnaks,
I run the command line for gpg with user1 and it creates encryption file and no error.
I run the JOb with the same user but it gave out error.
February 21, 2013 at 9:55 pm
When I changed success value from 0 to 2, it runs without any error but no encrypted file is then created.
Thanks.
February 21, 2013 at 10:08 pm
can this help?
http://stackoverflow.com/questions/6350442/gpg-error-code-2
Also make sure the succes value is the correct one. Most process has 0 for success. I gave you that suggestion only because you said the file was getting created
February 21, 2013 at 11:42 pm
I solved my issue.
Steps I made for Job run successful :
Create a Credential in SQL Server with the windows User Name.
Create Proxy with this Credential.
Run SSIS with Proxy.
This solved my issue and now generate encrypted file with SQL Server Job.
Thank you all who have help me a lot.
February 22, 2013 at 7:01 am
hsmanandhar 33101 (2/21/2013)
I solved my issue.Steps I made for Job run successful :
Create a Credential in SQL Server with the windows User Name.
Create Proxy with this Credential.
Run SSIS with Proxy.
This solved my issue and now generate encrypted file with SQL Server Job.
Thank you all who have help me a lot.
That sounds about right. What is the SQL Server Agent service account and what account did you use as the proxy?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply