February 25, 2002 at 7:20 am
? Created several DTS pkgs. All worked fine until today.
One of them failed this morning, checked the error log and states that could not complete the transformation task.
I am the only one with access to those pkgs and have not done any changes lately. I run the failed pkg manually and it did work fine.
Any suggestions why the scheduled job failed and the pkg worked fine manually? I am adding more error handling to the pkg, but I don't think the pkg is the problem (of course, I am always wrong!) and why this happened, after several moths of those pkgs working fine and sudenly only one fails?
Thank you
February 25, 2002 at 7:23 am
Has the SQL Agent account changed in any way?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 25, 2002 at 7:26 am
There can be numerous reason, do you have any more error details? Problems can occurr with packages out of the blue, the problem is unless starts happening often and you can produce sam results yourself it can be almost impossible to tell.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 25, 2002 at 8:17 am
Copied from a direct email:
quote:
No Brian, the agent remains the same. I have 81 packages picking up data from clients running very old systems, so I have to move all their data into SQL Server. Every single package is, basically the same. Some columns added by the clients. Out of the 81, I have at least 40 that have the old system exactly the same. One of them is the one whose pkg failed. Thank you
If you re-run the particular job that failed, can you re-run the job, and if so, does it fail with the same error? Every once in a while we'll get a DTS job failure out of the blue.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 25, 2002 at 8:22 am
Yes Brian. I a couple of times to see if the job was failing for some other reason. It did failed at the same point. That is why I run it manually and it completed successfully. That is why I am so curious about it. I have to figure it out in case the other pkgs start getting the same error, so far all worked fine. Thanks.
Do you know of any third party tool to help with those issues?
February 25, 2002 at 8:29 am
And the only error was the transform failed. Did any changes occur in the database or the file system or what have you with respect to specific permissions? Perhaps your user account still has rights but the service account does not.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 25, 2002 at 8:35 am
No Brian. Everything is exactly as always. In the event of any change on account or agent, should the others pkgs fail as well? Because of the nature of the pkg the only one auth. to run them is the DBA (me) And my account for all servers and databases are the same as sa. No changes at all
February 25, 2002 at 8:39 am
If none of the above then you may need to catch some more detail. If you re using SQL 7 open package properties and setup an error file, if 2000 turn on loggin and set up and error file to get details on the package execution and possibly the error information itself. Then when fails again you will have more detail on the failure than job error log stores.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 25, 2002 at 8:41 am
Oh just FYI...
On failure here is what my error file outputs, this is a simple example:
Package Name: New Package
Package Description: (null)
Package ID: {FE9D1E6F-9C70-4A59-A7E3-F3545CCDCA6C}
Package Version: {579B96D6-AE19-442D-AAEE-31795863F01B}
Package Execution Lineage: {784324C6-2F9A-45AE-83DB-1FED38B8C3B0}
Executed On: PINACLE
Executed By: a521886
Execution Started: 02/25/2002 10:21:30 AM
Execution Completed: 02/25/2002 10:21:31 AM
Total Execution Time: 0.2 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:Column name 'EmpID' was not found.
Step Error code: 80042008
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0
Step Execution Started: 02/25/2002 10:21:30 AM
Step Execution Completed: 02/25/2002 10:21:31 AM
Total Step Execution Time: 0.15 seconds
Progress count in Step: 0
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 25, 2002 at 8:51 am
Thank you. Antares, I setup my error log and I will check in more detail. Thanks As soon as I have that info I will come back to the site.
February 25, 2002 at 8:51 am
Thank you. Antares, I setup my error log and I will check in more detail. Thanks As soon as I have that info I will come back to the site.
February 25, 2002 at 11:08 am
Sorry for the late reply. Can you log into the server as the SQLAgent account and exucute them? Have you checked that none of the other packages have started running long and perhaps you have a blocking issue. Other than that, there are some good suggestions above.
Steve Jones
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply