The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Friday, March 29, 2024 Login
Public

DTS package, and knowing your error codes ! 12/23/2007 3:36:47 PM

First off it's always important to have lots of logging on for your dts packages.  These packages are difficult to troubleshoot, even with the logs.  So SET LOGGING ON, in the data pumps, in sql jobs, everywhere (don't forget you may need to have some clean up scripts to keep your log files from growing out of control).

Recently ran across a package, failing in production, ran fine of course in development and test.  Now this wasn't the greatest package, but unfortunately the DBA doesn't always get consulted on the design of things, but of course, it became the DBA Groups problem to fix!

The package has quite a few tasks, data pumps from Oracle to SQL and even runs an external c# program to pull data from Active Directory \ Exchange.

Nothing obvious for the error (it seems obvious to you realize it's bogus !), the log file is below.  Finally after researching and research could find nothing for why the "incorrect syntax error happenned".  To this day I beleive that to be a "bogus" error.  

I focused on that provider error of 170 (AA), remembering back to my ancient days of programming, I looked the error up:  AA        170 BUSY: The requested resource is in use  .  I began to throttle the parrallell processes to lower the amount of resources simultaneously in use by the package, ultimately I had to set it to 1.  It's run fine every day since.  I'm not sure if the Oracle provider interface on this machine is outdated and caused the error, or if the "requested resource in use" was some other item !

Don't forget those error codes... http://www.lifeasbob.com/Code/ErrorCodes.aspx

DTSRun OnError:  DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005)
   Error string:  Error at Destination for Row number 1. 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:  170 (AA)
   Error string:  Line 1: Incorrect syntax near 'OFFICE_ID'.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:  
   Help context:  0
 
DTSRun OnError:  DTSStep_DTSDataPumpTask_2, 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
 
 Error Detail Records:
 
Error:  -2147213206 (8004206A); Provider Error:  0 (0)
   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

Error:  -2147467259 (80004005); Provider Error:  170 (AA)
   Error string:  Line 1: Incorrect syntax near 'OFFICE_ID'.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file: 
   Help context:  0
 
DTSRun OnFinish:  DTSStep_DTSDataPumpTask_2
DTSRun:  Package execution complete.


Blog Home