Search code examples
sql-serverssisetlimplicit-conversionssis-2012

SSIS Data flow task implicit conversion automatically

I have following scenario:

  1. I have one data flow task with OLEDB source, taking data from source tables using query with inner join. One of the column is varchar(8) and few values are float, rest are int into this column.
  2. The OLEDB destination is storing above data into a table where corresponding column is INT.
  3. This package is deployed under SQL Server 2012 and SQL Job is executing this SSIS Package.
  4. This SSIS Package is getting executed successfully in one of our environment and doing implicit conversion, but in other environments its failing with Conversion Error, which is obvious.

So, my question is, why its not failing in one of our environment. could there be any environment specific, SSMS specific or SQL Job specific setting which is helping this job to be successful? Please help.

Solution

  • Based on this Microsoft article

    • Varchar can be implicitly converted to INT and Float
    • Float can be implicitly converted to INT and Varchar
    • INT can be Implicitly converted to Float and Varchar

    Implicit conversion

    I think that the Data that you are trying to import contains correct INT and Float values in the Varchar Column. On the other hand the data that you are trying to import on the other server contains values that cannot be converted (maybe special characters or additional spaces or alphabetic characters, ...) Make sure that your data is well pre-processed before importing it to the Destination