Conversion error

Question

Hi all,

Basically I am trying to create a package that will

(A) Create a table with specified datatypes

(B) Use a text Source file for the data

(C) on Success \ Completion of the "Execute SQL" transform the data from the text into the table.

Connect to DB <-- [TRANSFROM]-- Text (Source) <-- Execute SQL (Create Table)

It all seems to work now but when I run the package I get the following error

The number of failing rows exceeds the maximum specified.

TransformCopy 'DTSTransformation_6'conversion error: Conversion invalid for datatypes on column on pair 1 (source column 'Col007' (DBTYPE_STR),destination column 'Rec_Amt' (DBTYPE_CY)).

But when I go into the TransformDataTask, under transformation and test that column it all works fine, infact I tested all the columns and they all seem to work fine.

It also seems to be creating the same table twice first in the " Execute SQL" task and then again for some reason in the "DataTransform" task. I dont know if that is realted to the problem or not though.

Any idea's or suggestions I could try ?

Im very new to SQL 2000 & DTS so dont rule out any very newbie errors :)

Thanks

Answer 1

I'm not sure what steps you created, so I'm uncertain as to why it would duplicate the table. I would create one step to read the text file and create the table, and another to fill it. Here's a broad reference with guidelines, and if you have further questions you can check out Books Online for SQL Server 2000 to read more:

http://support.microsoft.com/default.aspx/kb/242377

Buck Woody

Answer 2

The number of failing rows exceeds the maximum specified.

TransformCopy 'DTSTransformation_6'conversion error: Conversion invalid for datatypes on column on pair 1 (source column 'Col007' (DBTYPE_STR),destination column 'Rec_Amt' (DBTYPE_CY)).

Looking at the error message the issue is very clear. The destination column 'Rec_Amt' datatype is money and that of the source column is string datatype.

It says string datatype can not be inserted into money datatype. Therefore please check the source file column 'Col007' for any unwanted character/text appearing there.

---

Regards, RSingh