SSIS conversion between types is not supported

  • Hi

    As part of an ETL conversion I have dataflow task created that copies data from one table to another, we use it as a way to rename fields and change data types. What is strange is that the OLE DB SQL destination has not issue if I copy data from an int to smallint and in another case from an int to a numeric field and it runs fine. So I decided to test using a SQL Server Destination as I heard it may be faste that using OLE Db.

    No I am getting the following error

    Error: 0xC02020F5 at Insert into TRANSACTION, SQL Server Destination [32]: The column "ID" can't be inserted because the conversion between types DT_I4 and DT_NUMERIC is not supported.

    Error: 0xC02020F5 at Insert into TRANSACTION, SQL Server Destination [32]: The column "ID1" can't be inserted because the conversion between types DT_I4 and DT_I2 is not supported.

    I do understand the error but not why it runs fine using the OLE DB but when running it through SQL Server Destination I get this error. If there is a solution can someone advise what I can do to get past this as well as explaining why the difference

  • Hi Andre,

    SSIS is checking the meta data of you columns.

    You would do an implicit conversion from INT to Numeric.

    This could lead to dataloss that cannot be controlled by SSIS.

    That is why it complains about the conversion.

    You can perform a data conversion task where you can convert the data.

    This would do an explicit conversion and SSIS would notice if this tasks fails.

    If you a using a select to get the source-data you can also use a convert in the select-statement and the data will be delivert as a numeric.

    Christian

  • Thanks will try that, still is strange that SQL Server destination complains but OLE Destination does not. I just read elsewhere that SQL Server destination may be dropped in future versions so maybe it's not even worth trying then

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply