Scheduled DTS always fails

I have a DTS that runs fine when executed at the package level. However, it
always fails when it runs as a scheduled job through the SQL Server Agent.
It always fails at ~93 seconds .
Anybody else experiencing this?
Here is the text for the failed scheduled DTS:
The execution of the following DTS Package succeeded:
Package Name: ORDER_HISTORY
Package Description: (null)
Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
Package Execution Lineage: {530DAB60-7837-406A-BDB3-37E577D79EE4}
Executed On: MSCSQL
Executed By: Administrator
Execution Started: 11/10/04 3:33:22 PM
Execution Completed: 11/10/04 3:34:55 PM
Total Execution Time: 92.984 seconds
Package Steps execution information:
Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
Step Execution Started: 11/10/04 3:33:22 PM
Step Execution Completed: 11/10/04 3:33:22 PM
Total Step Execution Time: 0.047 seconds
Progress count in Step: 0
Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY] Step'
failed
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[TOD][ODBC][Unknown]CONFIG: Expected a CONFIG call
Step Error code: 80074005
Step Error Help File:
Step Error Help Context ID:0
Step Execution Started: 11/10/04 3:33:22 PM
Step Execution Completed: 11/10/04 3:34:55 PM
Total Step Execution Time: 92.891 seconds
Progress count in Step: 72000
Step 'DTSStep_DTSExecuteSQLTask_1' was not executed
Step 'DTSStep_DTSExecuteSQLTask_2' was not executed
************************************************** **************************
****************
Here is the DTS log for the successful manually run DTS:
The execution of the following DTS Package succeeded:
Package Name: ORDER_HISTORY
Package Description: (null)
Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
Package Execution Lineage: {578A9ED8-A483-4D6B-A169-EB952D5AF27B}
Executed On: MSCSQL
Executed By: Administrator
Execution Started: 11/10/04 3:28:54 PM
Execution Completed: 11/10/04 3:32:35 PM
Total Execution Time: 220.906 seconds
Package Steps execution information:
Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
Step Execution Started: 11/10/04 3:28:54 PM
Step Execution Completed: 11/10/04 3:28:54 PM
Total Step Execution Time: 0.078 seconds
Progress count in Step: 0
Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY] Step'
succeeded
Step Execution Started: 11/10/04 3:28:54 PM
Step Execution Completed: 11/10/04 3:32:34 PM
Total Step Execution Time: 220.688 seconds
Progress count in Step: 118744
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 11/10/04 3:32:34 PM
Step Execution Completed: 11/10/04 3:32:34 PM
Total Step Execution Time: 0.078 seconds
Progress count in Step: 0
Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
Step Execution Started: 11/10/04 3:32:34 PM
Step Execution Completed: 11/10/04 3:32:35 PM
Total Step Execution Time: 0.016 seconds
Progress count in Step: 0
Michael,
DTS Packages run in the context of the calling process. You should check
permissions of the job, and also check to see if the connections are valid
for the job user. For example, if you run the package from Enterprise
Manager on another machine than the server, the connections may be valid,
but run from the server they may fail.
Jon Jahren
"Michael D. McGill" <mcgillmd@.hotmail.com> wrote in message
news:#4wQAV2xEHA.1076@.TK2MSFTNGP10.phx.gbl...
> I have a DTS that runs fine when executed at the package level. However,
it
> always fails when it runs as a scheduled job through the SQL Server Agent.
> It always fails at ~93 seconds .
> Anybody else experiencing this?
>
> Here is the text for the failed scheduled DTS:
> The execution of the following DTS Package succeeded:
> Package Name: ORDER_HISTORY
> Package Description: (null)
> Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
> Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
> Package Execution Lineage: {530DAB60-7837-406A-BDB3-37E577D79EE4}
> Executed On: MSCSQL
> Executed By: Administrator
> Execution Started: 11/10/04 3:33:22 PM
> Execution Completed: 11/10/04 3:34:55 PM
> Total Execution Time: 92.984 seconds
> Package Steps execution information:
>
> Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
> Step Execution Started: 11/10/04 3:33:22 PM
> Step Execution Completed: 11/10/04 3:33:22 PM
> Total Step Execution Time: 0.047 seconds
> Progress count in Step: 0
> Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY]
Step'
> failed
> Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
> Step Error Description:[TOD][ODBC][Unknown]CONFIG: Expected a CONFIG call
> Step Error code: 80074005
> Step Error Help File:
> Step Error Help Context ID:0
> Step Execution Started: 11/10/04 3:33:22 PM
> Step Execution Completed: 11/10/04 3:34:55 PM
> Total Step Execution Time: 92.891 seconds
> Progress count in Step: 72000
> Step 'DTSStep_DTSExecuteSQLTask_1' was not executed
> Step 'DTSStep_DTSExecuteSQLTask_2' was not executed
>
************************************************** **************************
> ****************
> Here is the DTS log for the successful manually run DTS:
> The execution of the following DTS Package succeeded:
> Package Name: ORDER_HISTORY
> Package Description: (null)
> Package ID: {64B36A3E-7E07-46B5-BE7B-6DED729B7F70}
> Package Version: {7A9368AF-3407-4708-9AFB-3CAFAD5ADB12}
> Package Execution Lineage: {578A9ED8-A483-4D6B-A169-EB952D5AF27B}
> Executed On: MSCSQL
> Executed By: Administrator
> Execution Started: 11/10/04 3:28:54 PM
> Execution Completed: 11/10/04 3:32:35 PM
> Total Execution Time: 220.906 seconds
> Package Steps execution information:
>
> Step 'Create Table [backend].[dbo].[ORDER_HISTORY] Step' succeeded
> Step Execution Started: 11/10/04 3:28:54 PM
> Step Execution Completed: 11/10/04 3:28:54 PM
> Total Step Execution Time: 0.078 seconds
> Progress count in Step: 0
> Step 'Copy Data from ORDER_HISTORY to [backend].[dbo].[ORDER_HISTORY]
Step'
> succeeded
> Step Execution Started: 11/10/04 3:28:54 PM
> Step Execution Completed: 11/10/04 3:32:34 PM
> Total Step Execution Time: 220.688 seconds
> Progress count in Step: 118744
> Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
> Step Execution Started: 11/10/04 3:32:34 PM
> Step Execution Completed: 11/10/04 3:32:34 PM
> Total Step Execution Time: 0.078 seconds
> Progress count in Step: 0
> Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
> Step Execution Started: 11/10/04 3:32:34 PM
> Step Execution Completed: 11/10/04 3:32:35 PM
> Total Step Execution Time: 0.016 seconds
> Progress count in Step: 0
>
|||Check the job owner, too. Jobs owned by users not in the sa role will not
run in the context of the sql server service.
How to run a dts package as a scheduled job:
http://support.microsoft.com/default...b;en-us;269074
"Jon Jahren" <jon.jahren.fightspam@.sqlkompetanse.no> wrote in message
news:%23C0Nt18xEHA.1308@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Michael,
> DTS Packages run in the context of the calling process. You should check
> permissions of the job, and also check to see if the connections are valid
> for the job user. For example, if you run the package from Enterprise
> Manager on another machine than the server, the connections may be valid,
> but run from the server they may fail.
> Jon Jahren
> "Michael D. McGill" <mcgillmd@.hotmail.com> wrote in message
> news:#4wQAV2xEHA.1076@.TK2MSFTNGP10.phx.gbl...
> it
Agent.[vbcol=seagreen]
> Step'
call
>
************************************************** **************************
> Step'
>