Re: Returning value from SQL Task

Tech-Archive recommends: Fix windows errors by optimizing your registry

Good day Charles,
Thank you for your reply.

My objective is actually trying to get the value returned from the DTS
package.

My flow is like this

1) Stored procedures calls DTS
2) DTS does work and return a value to show status
3) Stored procedure gets the value from 2)

But in no 2, one of my task is a SQL task. When it has completed, I would
like to change the value of the DTS global variable and send it back to my
stored procedure.

My question will be as follows.

1) How do I return a value from SQL task which will update the DTS Global
variable?
2) How do I return the DTS global variable to the stored procedure?

I undestand you have mentioned some solutions for my question 1. I have
actually have done similar steps but I still can't change the global
variable from my SQL task

Please advise. Thank you very much.

Regards,
Ronnie Tan

"Charles Wang[MSFT]" <changliw@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:EU1sSKhtGHA.1992@xxxxxxxxxxxxxxxxxxxxxxxx
Hi,

My understanding of your issue is:
You want to launch a DTS package in your stored procedure. However you
want
to know how you can set the DTS package so that you can get the return
value from the @check variable.
If I have misunderstood, please feel free to let me know.

You can set the DTS package parameters according to the following (SQL
2000):
1. Create a new SQL Task in DTS Package Designer;
2. Click the Properties of the task
3. In the open window, input the SQL statement and click the Parameters
button
4. In the open window, click the Create Global Variables button, and add
the parameter "checkStatus"; switch to the Output Parameters tab, select
the Row Value option in the Output Parameter Type, and under the Parameter
Mapping label,select Output Global Variable "checkStatus" on the column
that you want to map.
5. Click OK and save the package.

Then you can invoke your stored procedure in QA like this:
DECLARE @tt int
execute AAA_DTS_LoadStudentTemplate
@server_name='sqlservername',@pkg_name='packagename',@check=@tt output
select @tt

If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, a
nd confers no rights.
======================================================



.

Quantcast