Help - DTS package crashing with broken connection??

microsoft.public.sqlserver.programming

9 Sep 2005 11:42 PM

Richard

All,

Windows XP SP2 + hotfixes
SQL Server 2000 SP3

Has anybody seen this one? And is there a fix/workaround for it?

I have a DTS package that loads data in from CSV formatted flat files. The
package executes Ok for two small tables however it crashes about 10,000
records into a transformation task which is loading a flat file of 230,000+
records. Here's the error:

Step 'DTSStep_DTSDataPumpTask_3' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum
specified. (Microsoft OLE DB Provider for SQL Server (80004005): The
statement has been terminated.) (Microsoft OLE DB Provider for SQL Server
(80004005): Cannot insert duplicate key row in object 'Staff' with unique
index 'IX_StaffUserName'.)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0

Step Execution Started: 9/9/2005 3:41:52 PM
Step Execution Completed: 9/9/2005 3:42:07 PM
Total Step Execution Time: 15.391 seconds
Progress count in Step: 42000

The task runs a VBScript and has 1 Lookup - a check to see if a row exists
before inserting. The Lookup is using the same connection as the Load {which
I read is not good} but it must do so because the existence check is against
the table being loaded and loaded rows are not visible to other
connections...

Any ideas how I can a) circumvent this issue or b) allow load processing to
'quietly' fail on duplicate inserts but run to completion anyway?

10 Sep 2005 12:45 AM

Look like you got a duplicate key problem when inserting data into Staff
table, violation against index IX_StaffUserName. Clean out your data before
import doing the import again or import those data into a staging table with
no restriction and then clean out your data before import to the main table.

Q

Show quote

"Richard" wrote:

> All,
>
> Windows XP SP2 + hotfixes
> SQL Server 2000 SP3
>
> Has anybody seen this one? And is there a fix/workaround for it?
>
> I have a DTS package that loads data in from CSV formatted flat files. The
> package executes Ok for two small tables however it crashes about 10,000
> records into a transformation task which is loading a flat file of 230,000+
> records. Here's the error:
>
> Step 'DTSStep_DTSDataPumpTask_3' failed
>
> Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
> Step Error Description:The number of failing rows exceeds the maximum
> specified. (Microsoft OLE DB Provider for SQL Server (80004005): The
> statement has been terminated.) (Microsoft OLE DB Provider for SQL Server
> (80004005): Cannot insert duplicate key row in object 'Staff' with unique
> index 'IX_StaffUserName'.)
> Step Error code: 8004206A
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:0
>
> Step Execution Started: 9/9/2005 3:41:52 PM
> Step Execution Completed: 9/9/2005 3:42:07 PM
> Total Step Execution Time: 15.391 seconds
> Progress count in Step: 42000
>
> The task runs a VBScript and has 1 Lookup - a check to see if a row exists
> before inserting. The Lookup is using the same connection as the Load {which
> I read is not good} but it must do so because the existence check is against
> the table being loaded and loaded rows are not visible to other
> connections...
>
> Any ideas how I can a) circumvent this issue or b) allow load processing to
> 'quietly' fail on duplicate inserts but run to completion anyway?
>
>
>

10 Sep 2005 7:41 AM

Ian Evitable

I think this response from Louis was meanbt for you.

Chekc this KB article for the explanation:

http://support.microsoft.com/default.aspx?scid=kb;en-us;329329

Show quote

"Richard" <Rich***@discussions.microsoft.com> wrote in message
news:5A302543-FB5F-484B-9E7D-06943BEACC04@microsoft.com...
> All,
>
> Windows XP SP2 + hotfixes
> SQL Server 2000 SP3
>
> Has anybody seen this one? And is there a fix/workaround for it?
>
> I have a DTS package that loads data in from CSV formatted flat files.
The
> package executes Ok for two small tables however it crashes about 10,000
> records into a transformation task which is loading a flat file of
230,000+
> records. Here's the error:
>
> Step 'DTSStep_DTSDataPumpTask_3' failed
>
> Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
> Step Error Description:The number of failing rows exceeds the maximum
> specified. (Microsoft OLE DB Provider for SQL Server (80004005): The
> statement has been terminated.) (Microsoft OLE DB Provider for SQL Server
> (80004005): Cannot insert duplicate key row in object 'Staff' with unique
> index 'IX_StaffUserName'.)
> Step Error code: 8004206A
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:0
>
> Step Execution Started: 9/9/2005 3:41:52 PM
> Step Execution Completed: 9/9/2005 3:42:07 PM
> Total Step Execution Time: 15.391 seconds
> Progress count in Step: 42000
>
> The task runs a VBScript and has 1 Lookup - a check to see if a row exists
> before inserting. The Lookup is using the same connection as the Load
{which
> I read is not good} but it must do so because the existence check is
against
> the table being loaded and loaded rows are not visible to other
> connections...
>
> Any ideas how I can a) circumvent this issue or b) allow load processing
to
> 'quietly' fail on duplicate inserts but run to completion anyway?
>
>
>