SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS with PGP Encryption Failed when execute with SQL Server Job


SSIS with PGP Encryption Failed when execute with SQL Server Job

Author
Message
hsmanandhar 33101
hsmanandhar 33101
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 60
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??
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14939 Visits: 14396
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
hsmanandhar 33101
hsmanandhar 33101
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 60
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.
Roshan Jospeh
Roshan Jospeh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2087
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

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14939 Visits: 14396
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
hsmanandhar 33101
hsmanandhar 33101
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 60
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.
hsmanandhar 33101
hsmanandhar 33101
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 60
When I changed success value from 0 to 2, it runs without any error but no encrypted file is then created.
Thanks.
Roshan Jospeh
Roshan Jospeh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2087
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

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
hsmanandhar 33101
hsmanandhar 33101
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 60
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.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14939 Visits: 14396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search