The DTSRun statement runs without error, yet returns ErrorLevel 1.

Maxwell

unread,

Jun 27, 2005, 11:11:00 AM6/27/05

to

I am executing a DTSRun statement from a batch file running on Windows XP and
am checking for ErrorLevel 1 in order to perform error handling tasks. The
DTSRun statement runs without error, yet returns ErrorLevel 1.

I am using SQLServer2000 SP3:

SQL Server 2000 Service Pack 3:

select @@Version

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)

Example of the logic in the batch file that I am encountering the issue:

SET DTSLOAD=SUCCESS

if exist wait.exe wait 40
IF %TrustedConnection% == "N" DTSRun /N "DiscoveryDataMigrationStep1" /F
"DiscoveryDataMigrationStep1.dts" /A "SourceServer":"8"=%SourceServer% /A
"SourceDatabase":"8"=%SourceDatabase% /A "SourceLogin":"8"=%SourceLogin% /A
"SourcePassword":"8"=%SourcePassword% /A
"DestinationServer":"8"=%DestinationServer% /A
"DestinationDatabase":"8"=%DestinationDatabase% /A
"DestinationLogin":"8"=%DestinationLogin% /A
"DestinationPassword":"8"=%DestinationPassword% /A
"UseTrustedConnection":"8"="0" /L %LOGFILENAME% /W "1" | tee.exe
%LOGFILENAME% /A

IF ERRORLEVEL 1 SET DTSLOAD=ERROR

ECHO DTSLOAD: %DTSLOAD% >> %LOGFILENAME%

**********************************************
Results of Executing DTSRun statement

DTSRun OnProgress: DTSStep_DTSDataPumpTask_2; 156 Rows have been
transformed or copied.; PercentComplete = 0; ProgressCount = 156
DTSRun OnFinish: DTSStep_DTSDataPumpTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun: Package execution complete.
*******************************
--DTSLOAD: ERROR --

Helge C. Rutz

unread,

Jun 27, 2005, 2:03:25 PM6/27/05

to

Hi Maxwell,

"Maxwell" wrote:
>I am executing a DTSRun statement from a batch file running on Windows XP
and
> am checking for ErrorLevel 1 in order to perform error handling tasks.
> The DTSRun statement runs without error, yet returns ErrorLevel 1.

...

> **********************************************
> Results of Executing DTSRun statement
> DTSRun OnProgress: DTSStep_DTSDataPumpTask_2; 156 Rows have been
> transformed or copied.; PercentComplete = 0; ProgressCount = 156
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_2
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
> DTSRun: Package execution complete.
> *******************************
> --DTSLOAD: ERROR --

How do you know that the dts package runs without errors?
Please enable the logging of the dts package and see the detailed logfile
for informations of failed steps.
I know one scenario where the manipulation of the workflow of the package
by explicitly setting the execution status of steps can lead to this
behaviour.

Helge

Maxwell

unread,

Jun 27, 2005, 5:05:02 PM6/27/05

to

I reran the batch file after editing the DTS package to log execution to
SQLServer and to write errors out to a text file. Nothing was logged to the
text file and the
sysdtssteplog table didnt have any records with errorcode <> 0. I obtained
the same results - DTS package apparently succeeded, but ErrorLevel 1 was
returned.

"Helge C. Rutz" wrote:

> Hi Maxwell,
>
> "Maxwell" wrote:
> >I am executing a DTSRun statement from a batch file running on Windows XP
> and
> > am checking for ErrorLevel 1 in order to perform error handling tasks.
> > The DTSRun statement runs without error, yet returns ErrorLevel 1.

> ....

Helge C. Rutz

unread,

Jun 28, 2005, 1:12:20 PM6/28/05

to

Hi Maxwell,

"Maxwell" wrote:
>I reran the batch file after editing the DTS package to log execution to
> SQLServer and to write errors out to a text file. Nothing was logged
> to the text file and the

you mean no error was logged, didn't you? Please take a precise look at the
logfile.
There can be a strange behaviour, tellling you, that the package was
executed successfully at the beginning of the log, but in the step
execution list, there is an entry starting with "error on step ..." where
the step was never executed.
If this is the case, dtsrun will always return 1 as errorlevel, although
the package was mainly executed successfull.

If you haven't this scenario, I'm sorry, I haven't seen such behaviour
before and have no further ideas in the moment.

Helge

Maxwell

unread,

Jun 28, 2005, 2:09:04 PM6/28/05

to

Helge,

I also echoed the %errorlevel% variable to the log file :
ECHO DTSLOAD: %DTSLOAD% >> %LOGFILENAME%
ECHO ERRORLEVEL: %errorlevel% >> %LOGFILENAME%

and obtained:
DTSLOAD: ERROR
ERRORLEVEL: 4263136

Now I will look into the cause of this errorlevel value.