How to transfer data from SQL Server 2000 to Db2 V8 using DTS and Odbc driver

hi all,

I am trying to create a dts package to transfer data from tables in my
sql server 2000 database to a db2 UDB v8.1.4 database. When I use ibm
db2 oledb driver for the destination this works fine except that rows
are committed on a row by row basis thus performance is slow. I want
to use the odbc driver but when I define it as the destination, i get
errors saying that the maximum number of rows failed has exceeded the
minimum.

Does anybody know what to do in order to get the transfer working
using ibm db2 odbc as the datasource?

Thanks
Lyn"Lyn Duong" <lynd@.tablimited.com.au> wrote in message
news:8d1cda6d.0407061859.2b9da589@.posting.google.c om...
> hi all,
> I am trying to create a dts package to transfer data from tables in my
> sql server 2000 database to a db2 UDB v8.1.4 database. When I use ibm
> db2 oledb driver for the destination this works fine except that rows
> are committed on a row by row basis thus performance is slow. I want
> to use the odbc driver but when I define it as the destination, i get
> errors saying that the maximum number of rows failed has exceeded the
> minimum.
> Does anybody know what to do in order to get the transfer working
> using ibm db2 odbc as the datasource?
> Thanks
> Lyn

I guess the error message is actually "The number of failing rows exceeds
the maximum
specified"? That is a very general error, but there should be a more
detailed error message immediately after it in the log, which may give a
clue to the issue - can you post the full error message?

Simon|||Hi,

Yes, I logged the errors in an exception file and in an error file
I suspect that the odbc driver is not parsing the record properly

The exception file contains
DATA TRANSFORMATION SERVICES: Data Pump Exception Log

Package Name: (null)
Package Description: (null)
Package ID: {AE67DD38-6E64-4184-B5EB-6EE519F9CE9A}
Package Version: {AE67DD38-6E64-4184-B5EB-6EE519F9CE9A}
Step Name: DTSStep_DTSDataPumpTask_1
Execution Started: 8/07/2004 9:43:51 AM
Error at Destination for Row number 1. Errors encountered so far in
this task: 1. |1|609||||||||IS

The error file contains
The execution of the following DTS Package succeeded:

Package Name: (null)
Package Description: (null)
Package ID: {AE67DD38-6E64-4184-B5EB-6EE519F9CE9A}
Package Version: {AE67DD38-6E64-4184-B5EB-6EE519F9CE9A}
Package Execution Lineage: {6AE6BF2C-8187-4A9F-AA5F-2565B873DC51}
Executed On: ROULETTE
Executed By: lynd
Execution Started: 8/07/2004 9:43:50 AM
Execution Completed: 8/07/2004 9:43:51 AM
Total Execution Time: 0.25 seconds

Package Steps execution information:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Data
Pump
Step Error Description:The number of failing rows exceeds the maximum
specified. ( (80004005): )
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0

Step Execution Started: 8/07/2004 9:43:50 AM
Step Execution Completed: 8/07/2004 9:43:51 AM
Total Step Execution Time: 0.22 seconds
Progress count in Step: 0
************************************************** **************************************************

Any ideas ? Thanks!
"Simon Hayes" <sql@.hayes.ch> wrote in message news:<40ec34b4$1_1@.news.bluewin.ch>...
> "Lyn Duong" <lynd@.tablimited.com.au> wrote in message
> news:8d1cda6d.0407061859.2b9da589@.posting.google.c om...
> > hi all,
> > I am trying to create a dts package to transfer data from tables in my
> > sql server 2000 database to a db2 UDB v8.1.4 database. When I use ibm
> > db2 oledb driver for the destination this works fine except that rows
> > are committed on a row by row basis thus performance is slow. I want
> > to use the odbc driver but when I define it as the destination, i get
> > errors saying that the maximum number of rows failed has exceeded the
> > minimum.
> > Does anybody know what to do in order to get the transfer working
> > using ibm db2 odbc as the datasource?
> > Thanks
> > Lyn
> I guess the error message is actually "The number of failing rows exceeds
> the maximum
> specified"? That is a very general error, but there should be a more
> detailed error message immediately after it in the log, which may give a
> clue to the issue - can you post the full error message?
> Simonsql