December 10, 2007 at 5:51 am
I have a package I am executing through a DTS package. When I execute the package in Enterprise Manager it executes fine but it does not if I do it through a job or Query Analyzer. Any help please on how I can tell whats stopping it. It does not time out and if not stopped can go indefinate. I have another dts I execute just before this one and that goes through fine. The output I get in the logfile is as below:
The execution of the following DTS Package succeeded:
Package Name: mypackage
Package Description: (null)
Package ID: {8C3DBA03-3E48-4934-A3FC-44AE02C6E91D}
Package Version: {22EEB2E3-AA9C-4B45-AE3C-37F8272B55E3}
Package Execution Lineage: {4CEF559B-4EEF-4FA8-8E21-3597E75577F4}
Executed On: WILLOW5
Executed By: willowuser
Execution Started: 10/12/2007 12:16:21
Execution Completed: 10/12/2007 12:18:21
Total Execution Time: 120.313 seconds
Package Steps execution information:
Step 'DTSStep_DTSCreateProcessTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The process created by a task did not terminate within the time specified.
Step Error code: 8004040D
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4900
Step Execution Started: 10/12/2007 12:16:21
Step Execution Completed: 10/12/2007 12:18:21
Total Step Execution Time: 120 seconds
Progress count in Step: 0
December 10, 2007 at 7:34 am
Hello,
Could it be a permission problem with the SQL Server Agent account? Could it be a problem accessing a remote system?
Sincerely,
Dan B
December 11, 2007 at 5:59 am
The application does not access any other database and I am able to run another package using the same server so that rules out permissions.
December 11, 2007 at 12:05 pm
This is a long shot, but I had a similar issue when I updated some of the ODBC and OLE-DB drivers on one of my servers. I had a DTS package on Server-A that was sourced from Server-B and I had updated the drivers on Server-B. Whenever I would run the package manually, it would succeed, but when anything relating to the SQL Agent tried to execute it (a scheduled job for instance), it would fail. The reason seemed to have been that the Agent on Server-A had cached something relating to the connection to Server-B. I say that because I was able to stop and restart the SQL Agent on Server-A (which had not had its services restarted in several months) and my scheduled jobs started working again.
Like I said, it is a long shot, but you may want to try restarting the SQL Server Agent (or rebooting the machine entirely if you are able. If it doesn't solve the issue, at least it will rule those out.
December 11, 2007 at 1:51 pm
>>The application does not access any other database and I am able to run another package using the same server so that rules out permissions.
Not necessarily.
When you run this via Enterprise Manager, it runs under the credentials of the EM SQL registration.
When you run via an Agent job, it runs as whichever account the SQL Server instance is running under.
The 2 are usually not the same Windows account.
If a DTS package only accesses the SQL instance, there's usually not a problem.
However, if the DTS package has functionality that touches the filesystem you often run into issues with permissions to UNC shares, or drive mappings that exist for 1 account, but not the other.
March 9, 2010 at 10:16 am
I just went through this with an execute process task. I changed the timeout to zero in the properties. No more timeouts 🙂
It's confusing, because it looks like you need to check the timeout checkbox to enable timeouts, but that's not the case. They are enabled by default (60 seconds) regardless of whether the box is checked.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply