Unable to change datatype of external column in the advanced editor SSIS 2008

Question

In the advanced editor for the OLE DB Destination I am trying to change the datatype of a column from numeric to decimal. Each time I change it and refresh it goes back to numeric. If I execute the package I get the below error. Is there a way to have the datatype change permanent? 

[OLE DB Destination [21]] Warning: The external columns for component "OLE DB Destination" (21) are out of synchronization with the data source columns. The external column "NUMBER_OF_PAYMENT_EXPERIENCES_SLOW180PLUS" needs to be updated.

Answer 1

This is just a warning, and you must see a yellow mark beside the offending component.

You simply oftentimes need to open and close it to apply a refresh thus effectively clearing the warning.

The change however, appears permanent.

---

Arthur My Blog

Answer 2

You are trying to change the data type of a column on a OLE DB destination which derives its data type from the metadata of the actual table. So even though it allows you to update the data type it makes sense for it to go back to its original state because you are not modifying the actual table. Why don't you use a data conversion component/ derived column transformation before the OLE DB Destination and cast the column to numeric i..e match the data type of the actual table.

---

http://btsbee.wordpress.com/

Answer 3

When I open and close the component the warning goes away but the data type of the column changes back to numeric.

Answer 4

The data type of the actual table is decimal and I am trying to get the column on the OLE DB destination to match.

Answer 5

If thats the case you need worry about having to change the data type on the OLE DB Destination. The Decimal data type shows as NUMERIC in SSIS OLE DB Destination. Ignore any warnings. And if you get any errors they will be data related and not related to this mis-match issue.

---

http://btsbee.wordpress.com/

Answer 6

If that is the case then why does the data from the source file ( value 1.5) get imported to the destination column as (value 1.0)?

Answer 7

Please check the precision, scale on the column of your destination table. By default when you create a table in SQL, it sets decimal types as Precision 18 and Scale 0. Because the scale is 0 it will remove the decimal value. As said before any truncation/ error will be based on the setup/ design and not because of the above mis-match issue.

---

http://btsbee.wordpress.com/

Answer 8

Thanks btsbee

Changing the scale to 2 did the trick.