DTSRUN Change Global Variable Problem

[SQLBI]

Hi,

I'm attempting to run a DTS package and change the value of a Global Variable using 'dtsrun'.

My package simply takes the result of an SP and populates a .txt file. When i created my global variable (int datatype), i gave it a default value of 0 as it couldn't be left as null.

my code is as follows:

master..xp_cmdshell 'dtsrun /S192.168.1.21 /UMarketing /Pmarketing /N"Marketing - Healthcare_Status Recordset" 
		/APracticeID:typeid=3"101"/W"True"'

The code in my Transfer Data Task is as follows:

EXEC marketing..pr_Mailing_Healthcare_Status @PracticeID = ?

However the value of the GV does not change and the QA shows an output of:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Copy Data from vw_Healthcare_Status to C:\Healthcare_Status.txt Step
DTSRun OnProgress: Copy Data from vw_Healthcare_Status to C:\Healthcare_Status.txt Step; 0 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 0
DTSRun OnFinish: Copy Data from vw_Healthcare_Status to C:\Healthcare_Status.txt Step
DTSRun: Package execution complete.
NULL

Can anyone suggest where i'm going wrong and point me in the right direction?

Thanks in advance.

Cheers,
Leigh

The problem with common sense is that it isn't that common!

 

[rac2]

Try this syntax without the typeid

master..xp_cmdshell 'dtsrun /S192.168.1.21 /UMarketing /Pmarketing /N"Marketing - Healthcare_Status Recordset"
        /APracticeID:3="101" /W"True"'

 

[SQLBI]

Hi rac2, sorry that doesn't change the GV value and the output from QA is unchanged.

Cheers,
Leigh

The problem with common sense is that it isn't that common!

 

[rac2]

Maybe drop the quote marks around 101 since it is an integer, not a string.

For that matter, the /W parameter is a keyword, not a string, so True should work. However, are you getting an entry in the Windows Event Log showing the completion status.

Here is an amazing limitation on xp_cmdshell!

Syntax
xp_cmdshell {'command_string'} [, no_output]

Arguments
'command_string'

Is the command string to execute at the operating-system command shell. command_string is varchar(255) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is necessary if any spaces are present in the file paths or program names referenced by command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.

Also when you run the stored proc in Query Analyzer with a provider id of 101, how many rows do you get? Probably you have already checked that.

 

[SQLBI]

hi rac2

I've altered my code to exclude the Event Log entry (didn't write anything anyway) and removed the " from around my variable value.

master..xp_cmdshell 'dtsrun /S192.168.1.21 /UMarketing /Pmarketing /N"Marketing - Healthcare_Status Recordset"
        /APracticeID:3=101'

Still the QA output is the same and the .txt file is empty.

Any more suggestions?

Cheers,
Leigh

The problem with common sense is that it isn't that common!

 

[rac2]

Just the usual troubleshooting process. The guts of the transformation is a SQL query or a stored procedure, so run those and confirm that they produce the expected results.

Run the DTS manually with constants instead of parameters.

Go to a command line on the SQL Server host and enter the dtsrun command.

And so forth to identify where the issue lies.

From what you have posted and the corrections to the command string I think we have done all we can do.