DTS jobs getting false success, sometimes.

Last Post 28 Oct 2009 10:51 AM by johnbarry. 15 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
johnbarry
New Member
New Member

--
28 Oct 2009 06:10 AM
Recently upgraded from 2000 to 2005. Running DTS jobs until we have time to convert to DTS packages to SSIS. Several times we have gotten a "false success" on several jobs. By "false success" I mean that the job reports success, but it actually did not run or do anything. The message we get in the job is....'Executed as user: <username>. DTSRun: Loading... DTSRun: Executing... DTSRun: Package execution complete. Process Exit Code 0. The step succeeded.' As opposed to this message, when the job actually runs (failure or success) we normally get several lines of detailed run info.

We have verified that the job has not actually done anything. Data does not get loaded, tables are not deleted or created, etc... It is happening intermittenly with several different jobs, and not the same job on a daily basis. It seems to happen to a job that is running at the same time as another DTS job or TranslogBackup job. The schedule name happens to be the same name like "Daily 5:30am".

We are running SQL SERVER 2005 64bit Enterprise with SP3 applied on Windows 2008 Server (clustered). The packages are run from the job using the command...'DTSRun /S <server>\<instance> /E /N "DTSPackageName"'.

We may try to stagger the start times of these jobs, where we can, and see what happens.

I appreciate any suggestions or being pointed to any links that may be related to this.

Thanks!

John


SQLUSA
New Member
New Member

--
28 Oct 2009 06:26 AM
> Running DTS jobs until we have time to convert to DTS packages to SSIS

It appears that SQL Server 2005 sending you a strong message: make time to convert....

Once you convert, you will be happier. SSIS has extensively logging features so you can resolve issues fairly fast.

SSIS has a steep learning curve. However, you can get into it step-by-step.

Kalman Toth, Integration Services Training
http://www.sqlusa.com
johnbarry
New Member
New Member

--
28 Oct 2009 10:51 AM
We are putting the output of the step out to a file, which has basically the same info as the message. When they run successfully these files have much more detailed info. Today I edited the packages to include logging, although I a not expecting much as I dont believe the package is even running.

We have 23 DTS Packages set up to run as jobs. Mostly dealing with data from SQLServer, text files, excel files. 2 packages read from Oracle. All of these have run successfully over the last couple weeks, most of them on a daily basis. 7 of these jobs have produced this "false success" at random times (12 occurences total). One of the 7 reads from Oracle, the others strictly read/write to SQLServer on the same instance or another instance and read\write text or excel files on same server.

The Process Exit Code from windows is 0. The run_status from sysjobhistory is 1 (1 is success). Not seeing anything different in event logs or sqlserver logs.
johnbarry
New Member
New Member

--
29 Oct 2009 04:25 AM
The dts packages are in MSDB. Permissions shouldnt be an issue as the jobs run successfully most of the time. By successfully, I mean that the dts package was executed and all tasks were performed. Normally this is the case. Its only randomly that they give this "false success", with no rhyme or reason that I can find at this point.

Upgrade was done by migrating to a new server.
johnbarry
New Member
New Member

--
29 Oct 2009 05:04 AM
Below is a job that gave false success this morning. Below that is the results we got yesterday and most days. This is the second time this job has given "false success". Last time was on 10/21/2009. The only commonality that I am finding is the fact that other DTS jobs are running at the same time, but this wasn't an issue on 10/28/2009 at 6am.

-----------------------------------------------------------------------------------------------------------------------
Date 10/29/2009 6:00:00 AM
Log Job History (DTS:RSL to Warehouse)

Step ID 1
Server SQLDW\DW
Job Name DTS:RSL to Warehouse
Step Name RSL to Warehouse
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SA\SQLDW. DTSRun: Loading... DTSRun: Executing... DTSRun: Package execution complete. Process Exit Code 0. The step succeeded.

-------------------------------------------------------------------------------------------------------------------------
Date 10/28/2009 6:00:00 AM
Log Job History (DTS:RSL to Warehouse)

Step ID 1
Server SQLDW\DW
Job Name DTS:RSL to Warehouse
Step Name RSL to Warehouse
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SA\SQLDW. ...tart: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_7 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_7 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 5000 DTSRun OnProgress: DTSSte... Process Exit Code 0. The step succeeded.
-------------------------------------------------------------------------------------------------------------------------
johnbarry
New Member
New Member

--
29 Oct 2009 05:29 AM
Here is the DTSRun command we have in the job step....

DTSRun /S sqlDW\DW /E /N "RSL to Warehouse"

3 other jobs ran successfully at 6am
johnbarry
New Member
New Member

--
29 Oct 2009 06:22 AM
I found the below message in Windows event log. Not sure what it means, but looking into. Although, got the same message yesterday (below) when it ran fine.

--------------------------------------------------------------------------------------------------------------------------
Date 10/29/2009 6:00:00 AM
Log Windows NT (Application)

Source SQLAgent$DW
Category (3)
Event 1073742032
Computer SHDS.sp.uconn.edu

Message
The description for Event ID '1073742032' in Source 'SQLAgent$DW' cannot be found. The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them. The following information is part of the event:'DTS:RSL to Warehouse', '0x51EA79A09AF2A04A930D39F789E0658A', 'Succeeded', '2009-10-29 06:00:00', 'The job succeeded. The Job was invoked by Schedule 19 (Daily 6am). The last step to run was step 1 (RSL to Warehouse).'
--------------------------------------------------------------------------------------------------------------------------
Date 10/28/2009 6:00:01 AM
Log Windows NT (Application)

Source SQLAgent$DW
Category (3)
Event 1073742032
Computer SHDS.sp.uconn.edu

Message
The description for Event ID '1073742032' in Source 'SQLAgent$DW' cannot be found. The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them. The following information is part of the event:'DTS:RSL to Warehouse', '0x51EA79A09AF2A04A930D39F789E0658A', 'Succeeded', '2009-10-28 06:00:00', 'The job succeeded. The Job was invoked by Schedule 19 (Daily 6am). The last step to run was step 1 (RSL to Warehouse).'
--------------------------------------------------------------------------------------------------------------------------
johnbarry
New Member
New Member

--
29 Oct 2009 09:52 AM
yes. all work manually...in fact they all work as jobs, just not always.
johnbarry
New Member
New Member

--
29 Oct 2009 11:22 AM
I added DTS logging to every package yesterday (10/28). Not seeing anything more except an error.txt file gets produced for every package, which basically has the same detail as the job. The only difference is the job that had the "false success" had no Error.txt file. It really looks like the job just does not start the DTS package, but for some reason thinks it was successful.

Not really a 2005 issue because I found this forum which is exactly what we are getting, but they were using 2000. Unfortunately they did not put a resolution.
http://www.sqlmonster.com/Uwe/Forum...re-Message

Is there somewhere special to look for any additional DTS Logging? i've looked in SQL Logs, Windows, SQLAgent.out, and text files produced by job steps and DTS Packages.
johnbarry
New Member
New Member

--
29 Oct 2009 11:52 AM
For all jobs, Job owner is sa. The step is being Run As: SQL Agent Service Account (only choice). The SQL Agent Service Account is the SA\SQLDW, which is a domain account.
johnbarry
New Member
New Member

--
29 Oct 2009 12:09 PM
Yeah, thats been a thought, but if it works normally, not sure the full path should mean anything. We are leaning towards staggering the start times.

Pumping from SQL2005 to SQL2005, from/into SQL from/to text or excel files, or from Oracle to SQL. The jobs that have had the "false success" some or all of the above. The jobs that have not experienced this also do pretty much the same things. I dont think it is ever getting to the package level when it gives the "false success".
johnbarry
New Member
New Member

--
30 Oct 2009 04:10 AM
No that is the normal run. I had separated the different runs with lines. Here is the "false success"....

-----------------------------------------------------------------------------------------------------------------------
Date 10/29/2009 6:00:00 AM
Log Job History (DTS:RSL to Warehouse)

Step ID 1
Server SQLDW\DW
Job Name DTS:RSL to Warehouse
Step Name RSL to Warehouse
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SA\SQLDW. DTSRun: Loading... DTSRun: Executing... DTSRun: Package execution complete. Process Exit Code 0. The step succeeded.
-------------------------------------------------------------------------------------------------------------------------

johnbarry
New Member
New Member

--
30 Oct 2009 04:11 AM
...and here is the normal success..........

-------------------------------------------------------------------------------------------------------------------------
Date 10/28/2009 6:00:00 AM
Log Job History (DTS:RSL to Warehouse)

Step ID 1
Server SQLDW\DW
Job Name DTS:RSL to Warehouse
Step Name RSL to Warehouse
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SA\SQLDW. ...tart: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_7 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_7 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 5000 DTSRun OnProgress: DTSSte... Process Exit Code 0. The step succeeded.
-------------------------------------------------------------------------------------------------------------------------
johnbarry
New Member
New Member

--
06 Nov 2009 10:58 AM
We have changed the DTS jobs to use the full path for DTSRUN and see if that eliminates the "False Success" failures. If we still get them we will try staggering the times the jobs start next week.
johnbarry
New Member
New Member

--
09 Nov 2009 05:16 AM
Putting the full path for DTSRun did not work. Got several occurences of "false success" over weekend and this morning.

Will be changing job start times so that they are staggered and see if that eliminates the "false success" occurences.
johnbarry
New Member
New Member

--
25 Nov 2009 04:52 AM
since staggering the start times, just by a minute, we have had only 1 occurence since 11/9/09. That job has another DTS job running at the same time still. There were 2 jobs that ran every 15 minutes, accessing Oracle, that may have caused most/all of the "False Success" occurences. They are separated by a minute now. Will probably look to separate all jobs by at least a minute to eliminate this issue.

When I have a chance I will look into the options you mention (/G, /!X). Servers probably rebooted in October, prior to migrating to them from the SQL2000 servers. SQL Instances have been moved to different nodes since then to do windows maintenance, so the sql services have been restarted. Not sure the hotfix was applied during maintenance, but will look into.

Thanks for all your help on this.


Acceptable Use Policy
---