I'm having a problem getting data from a flat file to transform/load into a
MSSQL table. The transformation is a simple copy.

The transformation step fails with this message:
"Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum
specified. (Microsoft Data Transformation Services (DTS) Data Pump
(80042028): TransformCopy 'DTSTransformation__15' conversion error:
Destination overflowed on column pair 1 (source column 'Col015' (DBTYPE_STR),
destination column 'PrePNat' (DBTYPE_NUMERIC)).)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0"

The counter on the step said 13000. I used the step option and successfully
tried loading record 13000; also records 12599 through 13500.

The data type definition of the target field is decimal (9,3).
The total record count in the source is 17024. The destination db is setup
to autogrow. The filesystem has sufficient space.

My MSSQL @version is:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT
5.2 (Build 3790: )

Any help would be appreciated.

Thanks,
Steve L. Balto.

Hello Steve L. Balto. Steve L.,

The error is telling you that in the source you have a column called col015
and that is defined as a string. In the destination you have a column named
PrePNat and that is defined as NUMERIC data. These are mapped to each other.
There is data in the source that will simply not convert to that format.

Are you saying that if you do this in a 2 part load that all the rows load?

Allan

Allan,

Thanks for your response.

I solved the problem by turning on exception logging in the transformation
option tab (I had to deselect the SQL 7 checkbox to get the option to log
source and target exceptions). I then upped the allowable errors to 400 and
reran the pkg. After running, I viewed the source exceptions and see that 4
records starting after 13500 had values that have too many digits to the left
of the dec. place.

I upped the target precision and it works.



"Allan Mitchell" wrote: