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:
- Implicit conversion
- 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:
|
1 |
SELECT * FROM Table WHERE [StringColumn] = [NumericColumn] |
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:
- SSIS Source Format Implicit Conversion for Datetime
- CAST vs ssis data flow implicit conversion difference
- SSIS Data flow task implicit conversion automatically
Explicit conversion
Explicit conversions are visible to the user. It is performed using CAST or CONVERT functions or other tools. As an example:
|
1 |
SELECT CAST([NumericColumn] AS Varchar(50)) FROM Table |
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:
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:
Figure 2 – Data Conversion Transformation in SSIS Toolbox
The Data Conversion Transformation editor is not complicated; it is composed of two main parts:
- Input columns: This part is to select the columns that we want to convert their data types
- Data conversion configuration: This part is where we specify the output columns SSIS data types, and other related properties such as:
- Output Alias: Specify the output column name
- Length: Set the output column length for string data type
- Precision: Set the column precision for numeric data type
- Scale: Set the column scale for numeric data type
- Code Page: Select the code page for columns of type DT_STR
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:
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:
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:
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
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
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023