DTS and return codes 2

[Rosko]

Hi,
How can I implement a return code in a DTS package? To determine the next step (not possible to run from the DTS package itself) it is necessary to implement a return code.
How do I manage that? A code example would be great.

 

[tlbroadbent]

If you want to get a DTS return code in T-SQL, see the article and sample at the following link.

http://www.swynk.com/friends/green/dtsole.asp

If you are using VB, see the following...

http://www.sqldts.com/article.asp?nav=1,6,208,7,2

Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[Rosko]

It should give a return in a batch file when using the dtsrun command.


PS: Terry, Do you get anything done besides answering this forum! ;-)

 

[tkvgiri]

Hi
It's very simple. Create a new connection with Text File (Destination) and specify the path and file name. Select some existing SQL Server connection and the newly created Text file destination connections and select 'Add Transform' option under 'WorkFlow' menu. Open the work flow. In the source tab page, select the 'SQL Query Option' and type the query as "Select '1' As ExitCode".
Just select the destination and Transformations tab pages. Make sure that the output is transfered to destination.

While running the DTS, the output of Select statement will be saved into the text file.
Let me know your results.


Regards
TKVGiri
(velliangiri.t@blr.hpsglobal.com)

 

[Rosko]

Thanks for the info, this will surely help in the near future. Someone told me another way to, I'll will copy his answer here.


******************************
I just did this test in SQL 7.0.
I created a simple package that just had 1 Activex Script Task:
code:-------------------------------------------------------

Function Main()
Main = DTSTaskExecResult_Failure
End Function
------------------------------------------------------------

From the command-line I ran DTSRUN utility, after which I checked the status of errorlevel, the DOS error status variable and it was set to 1, error. A value of 0 is success.

c:\ dtsrun /S nike /E /Ptest /M
c:\ echo %errorlevel%

*****************************


So this is what I used.