Help - DTS package crashing with broken connection?

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 {whic
h
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?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
"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. Th
e
> 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 {wh
ich
> I read is not good} but it must do so because the existence check is again
st
> 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 t
o
> 'quietly' fail on duplicate inserts but run to completion anyway?
>
>|||I think this response from Louis was meanbt for you.
Chekc this KB article for the explanation:
http://support.microsoft.com/defaul...kb;en-us;329329
"Richard" <Richard@.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?
>
>