Hi,

How do you return a value from SQL task in DTS?

I have a scenario which goes like this

1) I have a stored procedure (refer below)

CREATE procedure AAA_DTS_LoadStudentTemplate --(@InConnection varchar (255))

@server_name varchar(20),
@pkg_name varchar(20),

@check int output

as

set nocount on

declare @pkg int, @rc int

--initialise value
set @check = -1

--Create an instance of a DTS package
exec @rc = sp_OACreate 'DTS.Package', @pkg output

--Load the baby up
exec @rc = sp_OAMethod @pkg
,'LoadFromSQLServer'
,null
,@Servername =@server_name
,@Flags = 256 --NT authentication
,@PackageName = @pkg_name


--Set any properties of the package
exec @rc = sp_OASetProperty @pkg
,'GlobalVariables ("taskStatus").Value'
,@check
--print @rc
--do it
exec sp_OAMethod @pkg
,'Execute'

exec @rc = sp_OAGetProperty @pkg
,'GlobalVariables ("taskStatus").Value'
,@check output
--print @rc
--destroy the instance
exec sp_OADestroy @pkg


--print @check
select @check
GO

2) After the package is launched, there is a SQL task in the DTS which is
suppose to return a value. This value should be returned to my @check
variable of my stored procedure.

Does anyone knows how to return a value from a SQL task?

Please help

Regards,
Ronnie

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='packagenam e',@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.
================================================== ====

"Charles Wang[MSFT]" <changliw (AT) online (DOT) microsoft.com> wrote

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 (AT) online (DOT) microsoft.com> wrote

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 (AT) online (DOT) microsoft.com> wrote

Hi Ronnie,
Thanks for your response.

I'm sorry that I can still understand your issue as my former reply.

In my former reply, I perform a test as you referenced:
1. Create the stored procedure as you show to me
2. In SQL Server 2000 Enterprise Manager, create a package, drag a
Microsoft OLE DB Provider for SQL connection and a SQL task on the task
panel; set the SQL OLE DB connection and select the database.
3. Double click the SQL task and in the input area of "SQL statement", I
input the SQL:
"SELECT * FROM FOOTBALL WHERE COUNTRY_ID = 4"
4. Click the Parameters button, switch to the "Output Parameters" tab,
click the "Create global variables..." button, input a global variable
"checkStatus", select the type int and set default value 0, click OK; on
the output parameters panel, select the "Row Value" option, then I select
one column of my test table "WINTIMES" and assign the output global
variable "checkStatus" to it.
5. Save the package.
6. Execute the stored procedure like this:
DECLARE @tt int
execute AAA_DTS_LoadStudentTemplate
@server_name='sqlservername',@pkg_name='packagenam e',@check=@tt output
select @tt
Then I got the value of @tt which accomodate the item value of the table
(it's also the global variable in DTS).

However, if it's convenient for you, I would like that you mail me
(changliw (AT) microsoft (DOT) com) your DTS package so that I could modify and send
it back to you.

If you have any other questions or concerns, please don't hesitate to let
me know. I would like for further assistance.

Charles Wang
Microsoft Online Community Support

Hi,

I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.

Have a great day!

Charles Wang
Microsoft Online Community Support