sql server dts: Returning value from SQL Task -- sql server dts

You're in the

sql server dts

group:

Returning value from SQL Task

RE: Returning value from SQL Task

changliw NO[at]SPAM online.microsoft.com
8/2/2006 9:03:41 AM

sql server dts:

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.
======================================================

Returning value from SQL Task

Hong Yip
8/2/2006 1:00:58 PM

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