execute DTSPackage using stored procedure with mulitple GlobalVariables

Question

execute DTSPackage using stored procedure with mulitple GlobalVariables

James Maynard

SSC Enthusiast

Points: 104
More actions
February 17, 2005 at 7:36 am

#166029

I am trying to execute a DTS package using a stored procedure and pass two globalvariables to the DTS package. The DTS package executes but uses the default globalvariable values instead of the ones passed. This is the dts command line being put together by the stored procedure.

dtsrun /Spsi /Usa /P***** /Nlfucg /A"periodstart":"8"="01/01/2005" /A"periodend":"8"="01/31/2005"
I have tried changing my globalvariable to "date" and changing the typeid to 7 but that didn't work either.
Please help.

Viewing 9 posts - 1 through 8 (of 8 total)

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

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 1

James,

Try leaving the Global Variables in the package empty (delete the values and save the package). I have had issue with this on occassion.

Good Luck,

Darrell

James Maynard SSC Enthusiast Points: 104 More actions · Answer 2

I tried that but the DTS package errors out now when trying to run because the globalvariables values are not coming in.

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 3

James,

I missed it in the original post - remove the quotes from around the 8 (typeid).

James Maynard SSC Enthusiast Points: 104 More actions · Answer 4

That didn't work either. If I set the globalvariable values in properties of the DTS package the package works but uses the values I put in the properties page. If I take them out the package fails. It seems like the globalvariables aren't being passed to the DTS package.

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 5

James,

I just created a test DTS package that writes the Global Variables values out to a text file. The variables are empty in the package. I executed the package from Query Analyser with the following statement:

xp_cmdshell 'DTSRUN /S (local) /E /N WTest /A"periodstart":8="01/01/2005" /A"periodend":8="01/31/2005"'

And it wrote the correct values out. The Global Variable names are case sensitive - other than that I am out of ideas at the moment.

James Maynard SSC Enthusiast Points: 104 More actions · Answer 6

WOW, that was it. I wasn't aware that the globalvariables were case sensitive. It is always the simple things that cause the biggest problems.

Thanks so much for all your help.

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 7

DSP-120308

Hall of Fame

Points: 3959

February 17, 2005 at 10:53 am

#542892

No Problem - glad it was that simple!!

Calvin Lawson SSChampion Points: 11030 More actions · Answer 8

Here's a clever solution (now that you already have one . But this works for all cases.

Under your SQLServer install, execute the utility called "dtsrunui.exe" (in Tools). Choose your package and click "Advanced". Set your global variables and click "Generate". voila! You have a DTSRun command all set up for you, including setting the global variables.

Much easier than doing that crap by hand.

cl

Signature is NULL