Search code examples
sql-serverssisetlsql-server-data-toolsssis-data-types

Change data types from Advanced Editor vs Data Conversion Transformation

I am using SSIS to create some packages. I have gotten confused around the Data Conversion Transformation component and changing columns data types from Advanced editor

If I can go into the advanced editor and change the data type of my output, why would I need to input the Data Conversion Transformation?

Is this just down to preference or is there a difference between using both approaches?

Solution

  • Before showing the difference between both approaches, i will try to give an overview of the data conversion types.

    Implicit vs Explicit conversion

    There are 2 different type of data type conversion:

    • Implicit conversion: Implicit conversions are not visible to the user. SQL Server automatically converts the data from one data type to another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.
    • Explicit conversion: Explicit conversions use the CAST or CONVERT functions or other tools.

    From the following Microsoft Grid you can see what data types can be converted with each approach:

    enter image description here

    If you need to know what are the SSIS data types relevant to the data types of SQL Server database engine (mentioned in the Grid above), then you can refer to the following documentation:

    Advanced Editor vs Data Conversion Transformation

    • When changing data types from the Advanced editor you are performing implicit data types conversion since the Source/Destination Component try to convert automatically the data type of the column.
    • Using Data Conversion Transformation you are performing explicit conversion. Since it is mentioned on the article above that:

    Use the Data Conversion transformation to cast the data type of a column from one data type to a different data type

    More details can be found at:

    References