Hadi Fadlallah
This image shows the sql data types and which types can be implicitly or explicitly converted

SSIS Data types: Change from the Advanced Editor vs Data Conversion Transformations

October 4, 2019 by

In this article, I will first give an overview of SSIS data types and data types conversion methods and then I will illustrate the difference between changing the columns data types from the Source Advanced editor and using Data Conversion Transformation.

This article is the seventh article in the SSIS feature face-to-face series, which aims to remove confusion and to illustrate some of the differences between similar features provided by SQL Server Integration Services.

SSIS Data types

When handling data using Integration Services Data Flow Task, the source data types are converted into SSIS data types. As an example, string data types are converted into DT_STR, DT_WSTR, DT_TEXT, DT_NTEXT data types.

Microsoft provided very detailed information about SSIS data types and related data types on the different data sources. For more information, you can refer to the following links:

The SSIS data types were founded to provide a unified set of data types that can handle different types from different sources. On the other hand, these data types have some limitations such as the minimum and maximum allowed values for the decimal data type, more detailed can be found at:

Data Types Conversion Methods

There are two types of data type conversion:

  1. Implicit conversion
  2. Explicit conversion

In this section, I will try to make a quick overview of each type.

Implicit Conversion

Implicit conversions are not visible to the user. Data types are automatically converted from one data type to another. For example, when a string is compared to an int, the string is implicitly converted to int before the comparison proceeds:

In SSIS, implicit conversion can be done using different methods, for example:

  • Mapping columns with different data types in the Destination component
  • Changing the column data type from Advanced Editor
  • Using a Script Component

For more information about implicit conversion, you can refer to the following posts on Stack overflow website:

Explicit conversion

Explicit conversions are visible to the user. It is performed using CAST or CONVERT functions or other tools. As an example:

In SSIS, Explicit conversion can be done using different methods, for example:

  • Using Data Conversion Transformation
  • Using Derived Column Transformation
  • (DT_WSTR,50)YEAR(GETDATE())
  • Using a Script Component

In this article, I will not describe the Derived Column Transformation, since it was explained in a previous article in this series: SSIS Derived Column with multiple expression Vs multiple transformation

Implicit conversion Vs Explicit conversion

Each pair of SSIS data types has its own case, you can find a pair that can be converted implicitly and another one that needs an explicit conversion.

Microsoft documentation contains a grid that illustrates which data types can be converted implicitly and which can be converted explicitly. This grid can be applied to SSIS data types since we mentioned in the above documentation that contains each SQL data type and its corresponding SSIS data type:

This image shows the sql data types and which types can be implicitly or explicitly converted

Figure 1 – Conversion data grid – Reference: Data type conversion (Database Engine)

Data Conversion Transformation

After describing the different types of conversion, we will give an overview of the Data Conversion Transformation and how it is used to perform data conversion.

Data conversion Transformation is a component used within data flow tasks to convert SSIS data types of input columns and generate new output columns:

This image shows the Data Conversion Transformation decsription from the SSIS tollbox

Figure 2 – Data Conversion Transformation in SSIS Toolbox

The Data Conversion Transformation editor is not complicated; it is composed of two main parts:

  1. Input columns: This part is to select the columns that we want to convert their data types
  2. Data conversion configuration: This part is where we specify the output columns SSIS data types, and other related properties such as:
    1. Output Alias: Specify the output column name
    2. Length: Set the output column length for string data type
    3. Precision: Set the column precision for numeric data type
    4. Scale: Set the column scale for numeric data type
    5. Code Page: Select the code page for columns of type DT_STR

This images shows the data conversion transfomation editor

Figure 3 – Data Conversion Transformation Editor

For more information about handling SSIS data types and Data Conversion Transformation, you can refer to the following official documentation:

Changing Data type from Advanced Editor

Another method to convert data types is changing the data types from the source component. To open advanced editor, right-click on the source component and click on Show Advanced Editor option:

This image shows how to open the source advanced editor to change the SSIS data types of the columns

Figure 4 – Show Advanced Editor

Note that when using flat file connections, you can change the SSIS data types from the flat file connection manager rather than using source advanced editor.

In the Advanced Editor dialog, go to the Input and Output Properties tab:

This image shows the Input and Output properties tab in the source advanced editor

Figure 5 – Input and Output Properties Tab

As shown in the image above, in the Inputs and Outputs tree view, under the Source Output node, you will see two nodes:

  • External Columns: represent the metadata of external data sources
  • Output Columns: represent the metadata of the columns used within the data flow task

When you expand any of these nodes, you can select any column and change its SSIS data type as shown in the image below:

This image shows how to change the SSIS data types of the columns using the advanced editor

Figure 6 – Change SSIS data type on the advanced editor

Discussion and conclusion

When you change a column data type using a data conversion transformation, or a derived column then you are performing a CAST operation which means an explicit conversion. While when you change the SSIS data types from the advanced editor, you are forcing the SSIS components to read the column as a different data type which means you are performing an implicit conversion.

There are many factors that you have to check before deciding which approach you have to use:

  • The logic you are implementing: Data conversion may be needed only at a specific point of the package execution, which means that you have to use a data conversion transformation. While if the data source column contains values stored in a wrong data type, you can use the advanced editor to change it back (Example: Excel text column that contains Numeric data)
  • The source and desired SSIS data types: As shown in the data conversion grid above, not all data types can be converted implicitly
  • Error handling logic: For example, if you are looking to get all values that cannot be converted, using a data conversion transformation may be more adequate since the error thrown are only related to conversion task, while source component may throw a different type of error which require a more generic error handling

Based on what we mentioned above, you have to choose which type of conversion you should go with based on the SSIS data types you are working with and what is the logic you have to implement within your data flow.

Table of contents

SSIS OLE DB Source: SQL Command vs Table or View
SSIS Expression Tasks vs Evaluating variables as expressions
SSIS OLE DB Destination vs SQL Server Destination
Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types
Execute SQL Task in SSIS: Output Parameters vs Result Sets
SSIS Derived Columns with Multiple Expressions vs Multiple Transformations
SSIS Data types: Change from the Advanced Editor vs Data Conversion Transformations
SSIS Connection Managers: OLE DB vs ODBC vs ADO.NET
SSIS Flat Files vs Raw Files
SSIS Foreach Loop vs For Loop Container
SSIS: Execute T-SQL Statement Task vs Execute SQL Task
SSIS Lookup transformation vs. Fuzzy Lookup transformation
SSIS Pivot transformations vs. Unpivot transformations
SSIS Merge Join vs. Merge Transformation
Data Access Modes in SSIS OLE DB Destination: SQL Command vs. Table or View
SSIS XML Source vs XML task
SSIS Script task vs. Script Component
SSIS term extraction vs. term lookup
Hadi Fadlallah
ETL, Integration Services (SSIS), SSIS monitoring

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah

168 Views