Dynamic Command in Execute Process Task

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Dynamic Command in Execute Process Task

Author

Topic

bdrhoa
Starting Member

6 Posts

Posted - 2006-07-11 : 19:45:47

I'm building a package that sets command of an Execute Process Task via a global variable. My problem is the Execute Process Task is returning a 1 (failure).

I create the command in a previous ActiveX step and use a msgbox to display the globalvar with the final command.

The command appears to correct. And if I create a separate package with just a Execute Process Task with the command and parms entered in the properties, everything works fine.

Any ides?

Thanks for the help.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-12 : 06:17:28

Not without a bit more info.
How are you setting the command to the built value?
When you set the entry you will set the command to the exe and parameters as a single string.
If you run in design mode you should be able to see how the process task has been changed.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bdrhoa
Starting Member

6 Posts

Posted - 2006-07-12 : 08:04:05

I'll try to explain my package. This may be more info than necessary, but someone might find it useful. The point of the package is to call a batch file that executes gpg to decrypt a file. This is the batch file gpg.bat which is in a directory in my path.


@echo off
gpg --passphrase-fd 0 <%1 -o %2 -d %3

The parms are %1 is a file containing the passphrase. %2 is the name of the decrypted file. %3 is the encrypted file.

Now for the package:

Step 1: asxCreatePassordFile - I create the passphrase file on the fly so it's not laying around on the server:


Function Main()

	Const fsoForWriting = 2
	Dim objFSO
	Dim objTextStream
	Dim theFile

	Set objFSO = createobject("Scripting.FileSystemObject")
	Randomize

	theFile =  DTSGlobalVariables("passwordFilePath").Value + "\"  + CStr( Int((99999999 * Rnd) + 10000001) ) + ".txt"
	

	 DTSGlobalVariables("thePasswordFile").Value = theFile

	Set objTextStream = objFSO.OpenTextFile(theFile, fsoForWriting, True)

	'Write the passphrase
	objTextStream.WriteLine "ldfjasldf"

	'Close the file and clean up
	objTextStream.Close
	Set objTextStream = Nothing
	Set objFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Step 2: axsBuildGPGCommand


Function Main()

	DTSGlobalVariables("pgpCommand").Value  = "gpg.bat " + DTSGlobalVariables("thePasswordFile").Value  + " "  +  _
							DTSGlobalVariables("outFile").Value +  " "  +  _
							DTSGlobalVariables("gpgFile").Value  


	Main = DTSTaskExecResult_Success
End Function

Step 3: setGPGCommand - This is a Dynamic properties task with:

Destination Property = ProcessCommandLine

Source Type = Global Variable

Soure Value = gpgCommand 'gpgCommand is a globalvar set in step 2

Step 4: exeGPG - This is the Execute Process Task that should be set by Step 3.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-12 : 09:31:52

When I do this I always do it from t-sql using xp_cmdshell to for the command.
It makes it a lot easier to test as you just have to form the string and can execute the same thing in query analyser as in production.
I would put this in a stored proc and call that with parameters for the filenames.
Any reason why you want to do this from DTS?

Your bat file is called gpg and so is the gpg app that it's calling - is that causing the problem?
As I said - I would do away with the bat file and code the call.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bdrhoa
Starting Member

6 Posts

Posted - 2006-07-17 : 13:07:19

I'm doing this in DTS because it's just 1 step to be reused by several, multi-step packages.

There was no problem with the file being named gpg.bat. Although I learned that BAT files get executed by the 16-bit subsystem, as old Windows (3x, 95, 98) command files. CMD is the extension for 32-bit versions of Windows (NT, 2000, 2003, XP). So I renamed my file .cmd.

In the end the only thing I was doing wrong was my code was setting pgpCommand when the exec step was expecting gpgCommand. Guess I should have specified Explicit Global Variables.

I did make one other change to my .cmd file. I added exit /B 0 to specify the return code.

Here's the final gpg.cmd


REM Copy this file to C:\Program Files\GNU\GnuPG
REM C:\Program Files\GNU\GnuPG must be in the PATH

@echo off

gpg --passphrase-fd 0 <%1 -o %2 -d %3
exit /B 0

BTW - The package has one additional step to delete the password file.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources