Output Parameter To Global Variable (DTS)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Paul Timmerman (PaulTimmerman_at_discussions.microsoft.com)
Date: 10/12/04 

Date: Tue, 12 Oct 2004 07:43:06 -0700

I have a DTS question. I am executing a stored procedure with 2 input
parameters and 2 output parameters.

The input params are working perfectly both when running the SP in QA and
when executing the DTS package.

The output params are causing me a headache. I can confirm that the
stored procedure outputs the parameters just fine (works when I test in
QA). When I execute the DTS task, only the first output param from the
stored procedure is populated into the global variable in the DTS package.
It is very puzzling to me as one of the output params works fine, so the
other should too, right? I can confirm that they are mapped properly.

The SQL task looks like (object names masked):

DECLARE @Output1 VARCHAR(128), @Output2 VARCHAR(128)
EXEC admin..storedprocedure ?,?, @Output1 OUTPUT, @Output2 OUTPUT
SELECT @Output1 AS Output1, @Output2 AS Output2

Both Output1 and Output2 show up on my screen for output parameters. I
simply map them to global variables I have created. Again, only the first
one works.

Please tell me I am missing something simple.....

TIA

Paul