Setting a global parameter via the execute command

Question

Setting a global parameter via the execute command

Holly

Newbie

Points: 3
More actions
November 7, 2001 at 6:44 am

#157742

Hello,
I am using a stored procedure, that receives a parameter from an ASP page, to execute my DTS package. This works fine, but I have not been successful in getting the DTS package to recognize and use the global parameter I am sending it. I am using the syntax "/A MyGlobalName:String value". The global is defined in the DTS package as a string. What else should I be doing to have the DTS package use the global setting I am sending it?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Paul Ibison SSCertifiable Points: 5303 More actions · Answer 1

Holly, could you post up the full command line you're sending so I can mimic it on my system. Also, how are you calling DTSRUN: from a cmdexec window on the server? Also, does the package definitely execute, but without using the global variable?

Paul Ibison

Paul.Ibison@btinternet.com

Paul Ibison
Paul.Ibison@replicationanswers.com

Holly Newbie Points: 3 More actions · Answer 2

Hi Paul,

I am executing a stored procedure via an ASP page. The DTS does execute, just without the global settings. Here is a copy of the stored procedure:

CREATE PROCEDURE doAutoCad03

@Log_File varChar(50)

AS

DECLARE @CmdScript varChar(225)

SET @CmdScript = 'dtsrun /S WIN2KSRV /N autocad_hjb1 /E /L C:\M2MData\acad import\Spreadsheets\'+@Log_File+'_log.txt /A DWG_Company:8=M2MData03 /A DWG_Drawing_Name:8='+@Log_File+'.xls '

exec master..xp_cmdshell @CmdScript

GO

NOTE: The passed parameter Log_File is being passed in correctly from the ASP as the log file is being created.

Thank you!

Paul Ibison SSCertifiable Points: 5303 More actions · Answer 3

Could try a couple of things to narrow it down. In the executesql task, or the first task in the package, look at workflowproperties/options/UseActiveX Script. Send out the value of the global variable in a msgbox (msgbox dtsglobalvariables("DWG_Drawing_Name").value, followed by the line main=dtsstepscriptresult_executetask. Next, run your command line in a command box on the server, and see what value appears in the message box. If it's not the new one, try editing the command line to be in the format:

dtsrun /S "WIN2KSRV" /N "autocad_hjb1" /E /L "C:\M2MData\acad import\Spreadsheets\xxx_log.txt" /A "DWG_Company":"8"="M2MData03" /A "DWG_Drawing_Name":"8"="xxx.xls" then run it and again check the value of the global variable - it should be ok.

Paul Ibison

Paul.Ibison@btinternet.com

Paul Ibison
Paul.Ibison@replicationanswers.com

Holly Newbie Points: 3 More actions · Answer 4

Thank you. It turned out that I had a spelling error in my global name. Your hint about displaying the globals in the msg box pointed this out to me. My original string worked once I corrected the spelling, no quotes needed. Thank you for all of your help.