Troubleshooting DTS errors
-
How do I see the complete messages returned from DTS packages that are ran inside jobs? The messages from the job history details relating to the package are cutoff. There is no info in the SQL logs or the event viewer (that I can see) about why the package is failing. Also there is no info in the DTS log. All I get is the "DTS.exe has generated errors" pop-up. Any help would be great. Thank you.
-
When you view the messages in the Job History window, are you checking the "Show Step Details" check box near the upper right of the window? Also do your jobs contain more that one DTS package/step?
Are you able to open the package in DTS Designer? If so, you could try executing each step of the package in order to see if you get a better error message.
I have never seen the pop-up that you mention...are you set up as an operator and notified if the job fails?
Let me know if I can be of further help...
-
Have you enabled logging to msdb in the package? That's always allowed me to view details of a package error.
Greg
-
Thank you for the reply. Yes, I have looked at the job step detail where the DTS task bombs. Here's the tail-end of it:"DTSStep_DTSDataPumpTask_16; 5000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 5000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_16; 6000 Rows have been transfor... Process Exit Code -1073741819. The step failed." That's a literal copy of the message, even the mispelling at the end. Notice the "..." before the exit code. I'm really trying to find out what's contained in the "..." if that's possible.
quote:
When you view the messages in the Job History window, are you checking the "Show Step Details" check box near the upper right of the window? Also do your jobs contain more that one DTS package/step?Are you able to open the package in DTS Designer? If so, you could try executing each step of the package in order to see if you get a better error message.
I have never seen the pop-up that you mention...are you set up as an operator and notified if the job fails?
Let me know if I can be of further help...
-
Greg thank you for the reply. Yes I enabled logging for the package. But nothing shows for the date when it errors out.
quote:
Have you enabled logging to msdb in the package? That's always allowed me to view details of a package error. -
did you try to use the dedugging by check the [Turn on just-in-time debugging]?
-
I'm guessing that we are dealing with a space limitation on the string that holds that error message and it looks like it backspaces over the tail of the string so that it can always show you the Process Exit Code at the end. Looking at msdb tables for DTS packages, I'd venture a guess that it's a varchar(2000).
In that case, I'd really suggest that you try another mehtod to determine the error.
I would start by tying to manually execute the package so it shows in the "Executing Package" window. The first benefit is that you will know the individual task within the package that fails and with luck, you may get a better error mesdsage than what you are receiving now.
A second strategy would be to open the package in designer and execture each step manually until you reach the failure.
I hope these suggestions are of use to you.
-
Thank you for your reply. I am not familiar with the just-in-time debugging option, but I will investigate and put into action. Very appreciated. Thanks again!
quote:
did you try to use the dedugging by check the [Turn on just-in-time debugging]? -
I was hoping it wasn't a literal truncate of the message, but looking at the table yes, that makes sense. I will try your stratedgy. Thank you once again.
quote:
I'm guessing that we are dealing with a space limitation on the string that holds that error message and it looks like it backspaces over the tail of the string so that it can always show you the Process Exit Code at the end. Looking at msdb tables for DTS packages, I'd venture a guess that it's a varchar(2000).In that case, I'd really suggest that you try another mehtod to determine the error.
I would start by tying to manually execute the package so it shows in the "Executing Package" window. The first benefit is that you will know the individual task within the package that fails and with luck, you may get a better error mesdsage than what you are receiving now.
A second strategy would be to open the package in designer and execture each step manually until you reach the failure.
I hope these suggestions are of use to you.
-
If package logging is enabled before the package is run then DTS will log execution info for each step in the package. It won't log anything if the package logging is enabled while the package is executing.
To view the Package Logs, just right-click on the Package in EM and choose 'Package Logs...' from then menu. In the dialog that comes up make sure you have the correct package selected. If your package isn't in the list then there are no log records to display.
To query the logs directly look at sysdtspackagelog, sysdtssteplog and sysdtstasklog in the msdb database. sysdtstasklog is usually empty as it's only populated for specific tasks and manual log entries.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
I do this a couple of ways.
1. Open the DTS package, right click for properties and on the looging tab under error handling specify an output file. This logs everything that the DTS package does.
If you're doing this, the file can get quite large over numerous executions so I always like to remove the old log when the DTS first starts. I create an activeX task like this:
Function Main()
dim objFSO
dim strLogFile
strLogFile = DTSGlobalVariables.Parent.LogFileName
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strLogFile) Then
objFSO.DeleteFile strLogFile, True
End If
Set objFSO = Nothing
Main = DTSTaskExecResult_Success
End Function2. My preferred option though is to specify a log file on the properties for each step within the SQL Agent job. This will include all the DTS output and other useful stuff too. You can also define whether the each output should overwrite or append.
Hope this helps,
. . Greg
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa -
Thank you for the reply Phillcart. I did check those tables out and I will keep that in the toolbox for later use. I checked the sysdts tables, package logs and step logs and came up with nothing. Probably because its actually a system error that has to do with calls to the ntdll?. When I got the "dts.exe has generated errors" pop-up there was a write to the Dr Watson log and there was a consistent pattern in two differents areas. 1) The dts package was erroring out pretty consistenly at about 1 minute. And when looking at the Dr Watson log I noticed the SQL Server calls were 1) different each time and, more importantly, they were sandwiched inbetween calls to the ntdll.dll. Now, we got hit with the Nachi virus last week when we were switching ISPs and had our accidently had our firewall down. This virus does exploit a buffer overrun vunerablity in the ntdll.dll. The server was indeed hit and subsequently cleaned and patched but my guess is that the virus software, recently upgraded, may be scanning and possibly locking this dll when it is being accessed by the dts package via the SQL server engine. This is just a guess, we will see. But Dr Watson and the fact that the Dts package was always bombing after 1 minute are my only clues to this perplexing problem. I will post the cause if it is ever found. Thank you again for the useful tips!
quote:
If package logging is enabled before the package is run then DTS will log execution info for each step in the package. It won't log anything if the package logging is enabled while the package is executing.To view the Package Logs, just right-click on the Package in EM and choose 'Package Logs...' from then menu. In the dialog that comes up make sure you have the correct package selected. If your package isn't in the list then there are no log records to display.
To query the logs directly look at sysdtspackagelog, sysdtssteplog and sysdtstasklog in the msdb database. sysdtstasklog is usually empty as it's only populated for specific tasks and manual log entries.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
-
Thank you for the insight. I forgot all about the right click/package log function! Please read my reply to phillcart about what I came up with as to the possible problem if you are interested. Thanks again!
quote:
I do this a couple of ways.1. Open the DTS package, right click for properties and on the looging tab under error handling specify an output file. This logs everything that the DTS package does.
If you're doing this, the file can get quite large over numerous executions so I always like to remove the old log when the DTS first starts. I create an activeX task like this:
Function Main()
dim objFSO
dim strLogFile
strLogFile = DTSGlobalVariables.Parent.LogFileName
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strLogFile) Then
objFSO.DeleteFile strLogFile, True
End If
Set objFSO = Nothing
Main = DTSTaskExecResult_Success
End Function2. My preferred option though is to specify a log file on the properties for each step within the SQL Agent job. This will include all the DTS output and other useful stuff too. You can also define whether the each output should overwrite or append.
Hope this helps,
. . Greg
-
Well I never did find out exactly what was causing the problem but after installing WIN2000 SP4 and changing the SQL SERVER AGENT connection login to sa, it now works. Thanks to all for your advise and insight.
Until next time, Cheers!
quote:
Thank you for the insight. I forgot all about the right click/package log function! Please read my reply to phillcart about what I came up with as to the possible problem if you are interested. Thanks again!quote:
I do this a couple of ways.1. Open the DTS package, right click for properties and on the looging tab under error handling specify an output file. This logs everything that the DTS package does.
If you're doing this, the file can get quite large over numerous executions so I always like to remove the old log when the DTS first starts. I create an activeX task like this:
Function Main()
dim objFSO
dim strLogFile
strLogFile = DTSGlobalVariables.Parent.LogFileName
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strLogFile) Then
objFSO.DeleteFile strLogFile, True
End If
Set objFSO = Nothing
Main = DTSTaskExecResult_Success
End Function2. My preferred option though is to specify a log file on the properties for each step within the SQL Agent job. This will include all the DTS output and other useful stuff too. You can also define whether the each output should overwrite or append.
Hope this helps,
. . Greg
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply