Job fails, but can't find what's wrong

Discussion in 'microsoft.public.sqlserver.dts' started by Amy, Aug 31, 2004.

  1. Amy

    Amy Guest

    I have a job transforming a foxpro database into a sql
    server database. When I run it on our production SQL
    Server, it fails; when we run it on a user's local SQL
    Server, it runs with no errors. The job FTP's a file,
    unzips it, then executes a transformation task. We've
    had problems with the agent's user not having access to
    the Foxpro drivers (which I still don't think I've
    resolved), but the weird thing is the job actually starts
    running now, and fails near the end. So it obviously is
    at least finding the Foxpro drivers now, because we can
    see the table is getting loaded. But the load fails
    before all of the rows are loaded, and this is the only
    error message I get in the "details" section of the

    Executed as user: GLOBUSANDCOSMOS\SQL. ...p -d
    y:\tech\cmsdataWarning: TZ environment variable not
    found, cannot use UTC times!! Archive:
    y:/tech/ inflating:
    y:/tech/cmsdata/EXPORT.DBF inflating:
    y:/tech/cmsdata/notes.dbf inflating:
    y:/tech/cmsdata/notes.fpt C:\WINDOWS\system32>del
    y:\tech\glb_exp.zipDTSRun: Loading... DTSRun:
    Executing... DTSRun OnStart: DTSStep_DTSFTPTask_1
    DTSRun OnFinish: DTSStep_DTSFTPTask_1 DTSRun OnStart:
    DTSStep_DTSCreateProcessTask_1 DTSRun OnFinish:
    DTSStep_DTSCreateProcessTask_1 DTSRun OnStart:
    DTSStep_DTSCreateProcessTask_2 DTSRun OnFinish:
    DTSStep_DTSCreateProcessTask_2 DTSRun OnStart:
    DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
    DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart:
    DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
    DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
    DTSStep_DTSDataPumpTask_3 DTSRun OnProgress:
    DTSStep_DTSDataPumpTask_3; 1000 Rows have been
    transformed or copied... Process Exit Code 1. The step

    Does anyone know where I can get a more detailed
    description of what is happening? Also, what kind of
    security do I need to set up to make sure the SQL Server
    Agent user has access to them? Thanks.

    Amy, Aug 31, 2004
    1. Advertisements

  2. The job step history which is what we see here is not very useful as it is
    truncated before we get to the meat of the matter.
    Have the package itself and the Datapump tasks log to text files ++ if you
    are calling the package in the job through a CmdExec step then look at the
    advanced tab of the step and specify an output file.

    When things go bent in a job but not through EM then you can usually figure
    things out by reading this article.

    Allan Mitchell, Aug 31, 2004
    1. Advertisements

  3. Amy

    Amy Guest

    Getting the error log worked, thanks! But now this is what it says:
    DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147467259 (80004005)

    Error string: Error at Destination for Row number 1186601. Errors
    encountered so far in this task: 1.
    Error source: DTS Data Pump
    Help file:
    Help context: 0

    Error Detail Records:
    Error: -2147467259 (80004005); Provider Error: 60 (3C)
    Error string: ROW-00060: Internal error: [dainsert,16]
    Error source: OraOLEDB
    Help file:
    Help context: 0

    DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147213206 (8004206A)
    Error string: The number of failing rows exceeds the maximum specified.
    Error source: Microsoft Data Transformation Services (DTS) Data Pump
    Help file: sqldts80.hlp
    Help context: 0

    Any ideas on what THAT means? Thanks so much for the help...
    Amy, Sep 2, 2004
  4. 80004005 is a general exception and usually relates to permissions but that
    is probably not the case here. What I think we may have is a problem with
    the driver and a row of data.

    On the last tab of the datapump in 2000 there is the option to throw bad
    rows out to a text file.

    Also set allowable bad rows to some high figure as well.


    Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) - The site for all your DTS needs. - Consultancy from the people who know

    Allan Mitchell, Sep 2, 2004
  5. Amy

    Viji Guest

    Viji, Sep 27, 2010
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.