SSIS debugging

Question

SSIS debugging

James Riehl

Ten Centuries

Points: 1154
More actions
December 20, 2007 at 7:04 am

#120053

OK...before you guys say anthing, I've got a Proxy that runs the package as the exact same user I use when I log in. So I don't think it's a permissions issue, That being said...
In my Execute Process Taks Editor you'd see the following values.
Executable:
C:\Program Files\GNU\GnuPG2\gpg.exe
Arguments:
--passphrase-fd 0 --decrypt-files FILETOBEDECRYPTED
Standard Input Variable:
(a variable containing my passphrase)
The success Value is set to 2 because the originator doesn't sign the file before they send it...(I think)
The Problem w/ doing this is just rolls right past w/ out decrypting anything.
Everything works great when I run the script in a command window, and when i run it in Visual Studio, however, when it's run in a SQL Agent Job it just doesn't do anything. The log progresses as if it worked, but nothing is being done.
Is there a better way to debug this?
Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

dstoeberl SSChasing Mays Points: 651 More actions · Answer 1

I had to do very nearly the exact same thing for a client a few months ago. The problem I had was not with permissions or SSIS or Agent, but with GPG. The problem I ran into was that the executable was running without access to the installed decryption keys, but this happened *only* when I tried to run it with the proxy credentials as an Agent job, never when I ran the package in the dev environment. (So it probably *was* a permissions error, just not in the way I thought.)

After some discussion with some colleagues I decided to modify the script component that calls GPG and add code to 1) check to see if the decrypt keys are installed and 2) install them if they aren't.

After this I redeployed and the script installed the keys the first time, and has run flawlessly ever since. I don't know if this is the issue you're running into, but it sounds similar enough I thought this might be some help.

hankmatt SSC Rookie Points: 32 More actions · Answer 2

I believe I have the same problem can you share the code that you wrote.

Thanks

dstoeberl SSChasing Mays Points: 651 More actions · Answer 3

I've handed off all the original code to other developers by now (that was three clients ago) but I still remember the gist of it (plus, now I think I have a better way of doing it):

The problem with my initial solution to this was that it required the GPG key to be left in an accessible location on the server. A much better way to do it would be to include a piece of code at the top of the script that would check to see if a file storing the GPG key is in a particular location accessible by the SS agent, and if so, import it. If not, skip ahead.

It would be something simple like this:

If System.IO.File.Exists("path to my file") Then

'import file here

End If

The particulars of importing the file should be in the documentation.

After that, you'd simply go on like normal. The first time the package runs, make sure that the key file is in the particular location that is checked by the script. After it has been imported, delete the copy you made (because you should have the original in a safe location). Then in the future, if the job runs under a different user or the encryption key changes, just drop the appropriate file into that location, and it should be picked up.

hankmatt SSC Rookie Points: 32 More actions · Answer 4

How do i get the key name once it has been imported?

dstoeberl SSChasing Mays Points: 651 More actions · Answer 5

This is GPG, right?

I don't think you need to specify the key name to use it. It's stored in the "keyring" for you and GPG just determines whether any of the keys can decrypt the file you give it.

You do have to specify the passphrase (I think passphrases are mandatory, but not sure). What I did for that part was execute this on the command line:

echo 'passphrase here'| gpg --passphrase-fd 0 --decrypt-files 'filename here'

adrinkwine SSC Veteran Points: 297 More actions · Answer 6

Ive run into similar issues with PGP and what ended up working for me was using variables to populate the arguments. I poulated the variables with all the arguments, then in the expressions on the Execute module, populating the arguments using that variable. It didnt like it when I hard coded the values in the arguments directly, but for some reason it worked this way. Maybe I had something else awry 😉

hankmatt SSC Rookie Points: 32 More actions · Answer 7

Yes it is gpg. I am encrypting files using a third party public key that changes once a quarter. I have it all working as long as I know the key name.

dstoeberl SSChasing Mays Points: 651 More actions · Answer 8

Hmm ... not sure if I can help with that. The code I gave was literally what I used for the decryption.

Here's where I would start:

http://www.gnupg.org/documentation/manuals/gnupg/Invoking-GPG.html

COZYROC One Orange Chip Points: 28499 More actions · Answer 9

Check CozyRoc's SSIS+ library, if you want straightforward solution. It is your choice. You either have time on your hands or you want to get the job done ASAP.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

adrinkwine SSC Veteran Points: 297 More actions · Answer 10

I had the same issue using GPG and my resolution was to log onto the server as the user for which the SQL Agent service was running under and import the keys. It worked like a charm then. (The Proxy account still isn't the that the job itself is actually running under).

This is a better solution than coding the package to import the keys every time because the keys need to be stored in a secure location and not just on any file server (otherwise it sort of defeats the purpose 😉 ).