SqlBulkCopy Error: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column.

 

Error Description:

The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column.

Reason:

The error occurs during sqlbulkcopy when the destination table contains the Decimal column with same precision and scale. (E.g., The  table in SQLServer has column TestColumn  Decimal (3,3) )

SELECT (cast(0.000 as decimal(3,3))) this will run fine in SQL, but will fail in bulk copy.

Possible Solutions:

Increase the precision size.

I had the same problem when I worked on my data migration project.

The work around was to increase the precision size by 1 if both the precision and scale are same for the Decimal Column type.

Example:

TestColumn Decimal (3,3) will fail in sql bulk copy.

But

TestColumn Decimal (3, 4) will work fine in sql bulk copy.