ssis 数据转换

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.

在本文中，我将首先概述SSIS数据类型和数据类型转换方法，然后说明从Source Advanced编辑器更改列数据类型与使用数据转换转换之间的区别。

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功能面对面系列文章中的第七篇，该文章旨在消除混淆并说明SQL Server Integration Services提供的相似功能之间的某些区别。

SSIS数据类型 (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.

使用Integration Services数据流任务处理数据时，源数据类型将转换为SSIS数据类型。 例如，字符串数据类型将转换为DT_STR，DT_WSTR，DT_TEXT，DT_NTEXT数据类型。

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:

Microsoft在不同的数据源上提供了有关SSIS数据类型和相关数据类型的非常详细的信息。 有关更多信息，您可以参考以下链接：

• Integration Services Data Types Integration Services数据类型
• Working with Data Types in the Data Flow 在数据流中使用数据类型

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:

建立SSIS数据类型是为了提供一组统一的数据类型，可以处理来自不同来源的不同类型。 另一方面，这些数据类型有一些限制，例如十进制数据类型的最小和最大允许值，有关更多详细信息，请参见：

• SSIS Data Types limitations SSIS数据类型限制

数据类型转换方法 (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:

隐式转换对用户不可见。 数据类型会自动从一种数据类型转换为另一种数据类型。 例如，当将一个字符串与一个int比较时，在比较进行之前，该字符串会隐式转换为int：

SELECT * FROM Table WHERE [StringColumn] = [NumericColumn]

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

在SSIS中，可以使用不同的方法来完成隐式转换，例如：

• 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:

有关隐式转换的更多信息，您可以参考Stack Overflow网站上的以下文章：

• SSIS Source Format Implicit Conversion for Datetime 日期时间的SSIS源格式隐式转换
• CAST vs ssis data flow implicit conversion difference CAST vs ssis数据流隐式转换差异
• SSIS Data flow task implicit conversion automatically SSIS数据流任务自动隐式转换

显式转换 (Explicit conversion)

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

显式转换对用户可见。 它是使用CAST或CONVERT功能或其他工具执行的。 举个例子：

SELECT CAST([NumericColumn] AS Varchar(50)) FROM Table

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

在SSIS中，可以使用不同的方法来完成显式转换，例如：

• Using Data Conversion Transformation

  使用数据转换转换

• Using Derived Column Transformation

  使用派生列转换

• (DT_WSTR,50)YEAR(GETDATE())

  （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

在本文中，由于本系列的上一篇文章已经解释了派生列转换，所以我将不对其进行描述：具有多个表达式的SSIS派生列与多个转换

隐式转换vs显式转换 (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.

每对SSIS数据类型都有其自己的情况，您可以找到一对可以隐式转换的数据对，以及另一个需要显式转换的数据。

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:

Microsoft文档包含一个网格，该网格说明可以隐式转换哪些数据类型以及可以显式转换哪些数据类型。 由于我们在上述文档中提到了该网格，因此可以将其应用于SSIS数据类型，其中包含每个SQL数据类型及其对应的SSIS数据类型：

数据转换转换 (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:

数据转换转换是数据流任务中使用的组件，用于转换输入列的SSIS数据类型并生成新的输出列：

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:数据转换配置：这部分是我们指定输出列SSIS数据类型以及其他相关属性的地方，例如：
   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 代码页：为DT_STR类型的列选择代码页

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

有关处理SSIS数据类型和数据转换转换的更多信息，您可以参考以下官方文档：

• Data Conversion Transformation 数据转换转换
• Convert Data Type by Using Data Conversion Transformation 通过使用数据转换转换来转换数据类型

从高级编辑器更改数据类型 (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:

转换数据类型的另一种方法是更改​​源组件中的数据类型。 要打开高级编辑器，请右键单击源组件，然后单击“ 显示高级编辑器”选项：

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.

请注意，使用平面文件连接时，可以从平面文件连接管理器而不是源高级编辑器更改SSIS数据类型。

In the Advanced Editor dialog, go to the 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:

展开任何这些节点时，可以选择任何列并更改其SSIS数据类型，如下图所示：

讨论与结论 (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.

当您使用数据转换转换或派生列更改列数据类型时，您将执行CAST操作，这意味着显式转换。 从高级编辑器更改SSIS数据类型时，您将强制SSIS组件将列读取为另一种数据类型，这意味着您正在执行隐式转换。

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) 您要实现的逻辑：仅在包执行的特定时间才需要数据转换，这意味着您必须使用数据转换转换。 如果数据源列包含存储在错误数据类型中的值，则可以使用高级编辑器将其改回（例如：包含数字数据的Excel文本列）
• The source and desired SSIS data types: As shown in the data conversion grid above, not all data types can be converted implicitly 源和所需的SSIS数据类型：如上面的数据转换网格中所示，并非所有数据类型都可以隐式转换
• 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.

基于上面提到的内容，您必须根据正在使用的SSIS数据类型以及在数据流中要实现的逻辑来选择应该进行哪种转换。

目录 (Table of contents)

翻译自: https://www.sqlshack.com/ssis-data-types-change-from-the-advanced-editor-vs-data-conversion-transformations/

ssis 数据转换