Solved

Encrypt file in SSIS using PGP

Posted on 2008-10-27
20
2,444 Views
Last Modified: 2013-11-10
Im trying to encrypt files using PGP command line in SSIS.

I have installed GNU PGP command line version [gnupg-w32cli-1.4.9.exe]

I  have tried changing the arguments in 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.
0
Comment
Question by:analliah
  • 10
  • 9
20 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22814990
Hi,
You should use an Execute Process Task, not SQL Task.
Assuming you are, are you running this on your local machine - local C:\drive?
What are your Execute Process Task Editor properties set to?

execute-process-task-editor.bmp
0
 
LVL 5

Author Comment

by:analliah
ID: 22815048
My bad...Im using Excute Process Task

The executable is set to  = C:\Program Files\GNU\GnuPG\gpg.exe
Arguments = gpg --recipient "XXX" --output "C:\Testing.zip.gpg" --encrypt"C:\Testing.zip"

Where XXX refers to Publice Key used for encrypting .

The execution of SSIS does not return any errors, but no .gpg file gets created
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22815080
Remove the gpg from the argument list. Isn't that the executable?
Just: --recipient "XXX" --output "C:\Testing.zip.gpg" --encrypt"C:\Testing.zip
0
 
LVL 5

Author Comment

by:analliah
ID: 22815170
It works fine now.Thank you :)

How can I pass the file name as variable to the argument .

 As I have 2 files that need to be encrypted one after the other.

File names are as A_20081027.zip & B_20081027.zip.

0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22815215
Sure, use an expression. Create a variable to hold the dynamic elements like filenames. In the Editor above, select Expressions and assign the varialbe to the expression.
0
 
LVL 5

Author Comment

by:analliah
ID: 22815295
Could you give me more explanation.I have created the following.

I have created 3 variables.
1 Variable - Holds Public Key
2 Variable - File to be encrypted
3 Variable - After encryption file name

Where can I specify the folmat of the 2 file names ?
0
 
LVL 5

Author Comment

by:analliah
ID: 22815491
I tried scheduling the SSIS package as a job and it returned me the following error .

Source: PGP Encrypt Execute Process Task     Description: File/Process "gpg.exe" does not exist in directory "C:\Program Files\GNU\GnuPG".  End Error  Error: 2008-10-27 14:40:38.11     Code: 0xC0024107     Source: PGP Encrypt      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22815528
OK, before scheduling - remember that the C:\ drive is probably not a good server drive. Use fully qualified paths, ie: \\server\directory\folder\file.exe
I will send the steps for expression build.
0
 
LVL 5

Author Comment

by:analliah
ID: 22815617
Ok I will try changing to the path as advised.

Do send me steps for the expression Builder.Thank You
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 17

Expert Comment

by:HoggZilla
ID: 22815697
Here is the start of an expression and a few screen shots:
"--recipient "
+ "\""
+  @[User::Key]
+ "\" "
+ "--output "
+ "\""
+ @[User::FileOne] + ".gpg"
+ "\" "
+ "--encrypt "
+ "\""
+ @[User::FileTwo]
+ "\""
Notice the \ used as an exception character to show quotes in the expression

expressions-arg.bmp
expression-editor.bmp
expression-builder.bmp
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22815772
For each one of the Filename variables, you can set the property EvaluateAsExpression to TRUE and then write an expression to build the FileName. Something just like above.
If you put a "\" slash or " quote in your expression be sure to prefix it with a slash \, it is the escape character. So a path like \\server\dir\file.exe would look like this \\\\server\\dir\\file.exe
Good luck.
0
 
LVL 5

Author Comment

by:analliah
ID: 22815925
Ill be giving  it a try .Thank You
0
 
LVL 5

Author Comment

by:analliah
ID: 22816282
Here is what I have done so far.
Assume file name to be encrypted is A_20081027.zip & B_20081027.zip.Both located in C drive.

I created 2 variables [ User:: Key ] -Holds the Public Key &
[ User :: FileOne] - File name that is being passed from For Each Loop

I want the PGP command line to be executed for both the above file as long as they have .zip extension and todays date.

I added a For Each Loop Container
Set the following
Expression : (DT_WSTR,4)DatePart("yyyy", GetDate()) +  (DT_WSTR,2)DatePart("mm", GetDate()) + (DT_WSTR,2)DatePart("dd", GetDate()) =Returns 20081027
Folder : C:\
Files : *.zip
Retrieve File Name : Name and Extension
Variable Mapping : [ User :: FileOne]

In For Each Loop Container,I placed The Execute Process Task.
Argument > Expression
"--recipient "
+ "\""
+  @[User::Key]
+ "\" "
+ "--output "
+ "\""
+ @[User::FileOne] + (DT_WSTR,4)DatePart("yyyy", GetDate()) + (DT_WSTR,2)DatePart("mm", GetDate()) +  (DT_WSTR,2)DatePart("dd", GetDate()) + ".zip"  + ".gpg"
+ "\" "
+ "--encrypt "
+ "\""
+ @[User::FileOne]  +  (DT_WSTR,4)DatePart("yyyy", GetDate()) + (DT_WSTR,2)DatePart("mm", GetDate()) +  (DT_WSTR,2)DatePart("dd", GetDate()) + ".zip"
+ "\""
Returns : --recipient "XXX" --output "C:\20081027.zip.gpg" --encrypt "C:\20081027.zip"

This fails when it executes and returns the following
[Execute Process Task] Error: In Executing "C:\Program Files\GNU\GnuPG\gpg.exe" "--recipient "XXX" --output "20050530ForeachLoop.zip20081027.zip.gpg" --encrypt "20050530ForeachLoop.zip20081027.zip"" at "", The process exit code was "2" while the expected was "0".

How can I pass the 2 file names?

0
 
LVL 5

Author Comment

by:analliah
ID: 22816341
20050530ForeachLoop.zip Is the name of another zip file in C:\.

Why does it pick this when I stated expression as todays date
0
 
LVL 5

Author Comment

by:analliah
ID: 22816402
It encrypts all file's in C:\ that has a *.zip extension regardless of the timestamp expression.

Any idea why ?

Thanks
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22816465
If you are looking through a folder to find all *.ziip files in a Foreach Loop Container, all files found will receive the same processing that is enclosed in the Container. If you need the FileSpec changed to find a specific filename, like one with the concatenated date format you show above, then create an expression for the FileSpec property of the Foreach Loop Container. Today it would evaluate to something like this:
*_20081027.zip
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22816525
Further, the filename variable expression might look like this.
FileSpec (expression)
"\*_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) +  (DT_WSTR,2)DatePart("mm", GetDate()) + (DT_WSTR,2)DatePart("dd", GetDate()) + ".zip"
0
 
LVL 5

Author Comment

by:analliah
ID: 22816645
Thank You very much.I got it to work.Its only encrypting file with todays date.

Not sure if I can post another question in regards to the above but for VB.Net

I have also created a function in VB.Net to perform encryption as below.

            Dim GPG As New FileInfo("C:\Program Files\GNU\GnuPG\gpg.exe")
            Dim sGPGLocation As String
            Dim sCommandLine As String = Nothing
            Dim extension = ".gpg"
            Dim gpgEncryptName As String = "C:\Testing.zip"
            Dim publicKey As String = "XXX"
            '-------Ensure we can find GPG.exe------
            If GPG.Exists = True Then
                sGPGLocation = "C:\Program Files\GNU\GnuPG\gpg.exe"
            Else
                Console.WriteLine("Error: Couldn't find GPG.EXE")
                'Return False
            End If

sCommandLine = """ --recipient """" & """" publicKey & """" --output """" & """" gpgEncryptName """" & """" extension & """" --encrypt """" & """" gpgEncryptName """
Console.WriteLine(sCommandLine)
Shell("""--recipient & """ """ & publicKey & """ """ & --output & """ """ & gpgEncryptName """" & """" extension & """" --encrypt """" & """" gpgEncryptName """)
            'Return True
        Catch ex As IOException
            Console.WriteLine("Unable to locate GPG Software")
            'Return False
            Console.WriteLine(ex.Message)
            Console.ReadLine()
        End Try

Im not very sure on the sCommandLine string.Could you tell me how I can represent the above similar to the argument I have passed to SSIS.Im not sure on how I can place the """" &  """.


0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
ID: 22816714
The VB.net part is a little off the subject. Probably better get an Expert on that to respond. In your Shell command, I think you need to start with the gpg.exe. Good luck, glad I could help.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now