Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Unanswered: DTS works but job fails

    Hi

    I have a DTS to copy data from Oracle to SQL Server. When I logon to SQL Server box with a userID xxx, I can run the DTS from EM and it works perfectly fine but when I schedule the DTS as job, it fails.

    SQL Server agent is running with same account "xxx"
    DTS connects to SQL Server with sa authentication
    Job owner is same account "xxx"

    Job error log

    Executed as user: DOMAINNAME\xxx. ... Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart... Process Exit Code 6. The step failed.


    I copied the DTS to another one and scheduled it

    This time I got the error log
    Executed as user: DOMAINNAME\xxx. ...... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnStart: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnError: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step, Error = -2147467259 (80004005) Error string: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed. Error source: Microsoft OLE DB Provider for Oracle Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (. The step failed.

    Please help!
    Thanks in advance

  2. #2
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    when you say you logon to SQL Server box you mean via remote desktop connection? or are you connecting EM on your machine to the server and then running the DTS?

  3. #3
    Join Date
    Dec 2007
    Posts
    2
    I connect via remote dektop connection by using same windows NT login which is runing SQL Server agent.

  4. #4
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    this often happens because of the fact that EM and Job use different runtime security context. in simple word this might mean that a path to an external file as "c:\myfolder\myfile.csv" may work for EM but NOT for Job even if it is the same DTS both are using in background.

    try using full path/name for file/resource like "\\networkame\drive\...." . check for necessary rights as well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •