在Package中处理 bit column

逻辑类型是最常用的数据类型之一,一般编程语言,例如,C#、Java和R等都支持布尔类型,用于表示逻辑真(true)和假(false),然而,SQL Server没有纯的布尔类型,但是,在编程时,可以使用bit 类型来代替逻辑类型,bit类型只有两个有效值:0 和 1。在设计数据表架构时,使用0代表逻辑假,使用1代表逻辑真。当SQL Server的数据表作为外部数据源时,SSIS引擎需要把数据加载到缓存,由于SSIS引擎内置布尔类型,它会自动把bit 类型转换成布尔类型,把 1 转换成 True,把0 转换成False,转换的流程时透明的。

在SQL Server的语法中,bit类型的0和1,跟整数类型的0和1是不同的:

  • 当把整数类型转换为bit类型时,SQL Server把整数0转换为bit类型的0,把非整数0转换为bit类型的1。
  • 当把bit类型转换为整数类型时,SQL Server把bit类型的0转换为整数0,bit类型的1转换为整数类型的1。

但是,当SSIS引擎把布尔类型转换为整数类型输出到SQL Server时,转换的流程是:

  • SSIS引擎把False转换为整数0,True会转换为整数-1

一,逻辑类型的转换

SSIS引擎会自动转换外部数据源的类型,把外部数据源的类型转换为SSIS支持的数据类型。SSIS内置的原生数据类型的命名格式是DT_xxxx,关于SSIS数据类型的详细信息,请查阅:SSIS 数据类型和类型转换

如果外部数据源是SQL Server,列的数据类型是bit,那么Package在加载数据时自动进行类型转化,把bit类型转换为DT_BOOL类型,转换的过程是把 1 转换成 True,把0 转换成False。在输出数据时,把DT_BOOL类型转换为外部数据源支持的逻辑类型,如果外部数据源是bit类型,那么转换的过程是把True转换为1,把0转换为False,转换过程是透明的。

由于bit类型和DT_BOOL是兼容的,所以转换之后,数据仍然是等价的,但是,如果要把SSIS的DT_BOOL类型转换成外部数据源的Int 类型(0,1),或转换成 String 类型(“0” 或 “1”),而不是(“True”或“False”),直接转换是有风险的,转换的过程可能是不等价的。这就需要增加一个派生列,使用SSIS的表达式,通过显式类型转换来实现,例如:

IsFlagged_bit = [IsFlagged] ? (DT_UI1) : (DT_UI1)

在Package中处理 bit column

二,探究bit类型和整数类型的转换

使用以下脚本创建测试数据,表TestBit中有一列IsMerged是bit类型,插入两行数据,IsMerged的值分别是0和1:

create table dbo.TestBit
(
ID int,
name varchar(),
IsMerged bit
) insert into dbo.TestBit
values(,'a',),(,'b',)

1,在Package中检查bit类型

在Package中创建数据流任务,使用OLE DB Source 来获取TestBit的数据,设置OLE DB源的连接管理器:

在Package中处理 bit column

点击按钮“Preview...”,看到IsMerged 字段的值不是0 或1, 而是被SSIS转换成True 或False:

在Package中处理 bit column

2,把bit类型的数据插入到整数类型中

SQL Server中的整数类型是指smallint、int和bigint,使用OLE DB Destination,把bit 类型的数据直接插入到目标表整数列中,例如,把dbo.TestBit的数据插入到表dbo.TestBit_Target中。

create table dbo.TestBit_Target
(
ID int,
name varchar(10),
IsMerged smallint
)
go

创建映射关系如下图所示,输入列IsMerged是DT_BOOL类型,目标列IsMerged是DT_I2类型:

在Package中处理 bit column

从映射关系中,可以看出,转换实际上发生在类型DT_BOOL和 类型DT_I2之间,即是SSIS支持的原生类型之间的相互转换,虽然DT_BOOL和 DT_I2之间的转换是合法的,SSIS引擎不会抛出任何异常,但是转换的结果是不等价的。

在Package中处理 bit column

3,查看插入的结果

从SQL Server数据库中查看插入的结果,当把外部数据源的Bit类型的数据转换为smallint时,SSIS 把0(bit)转变成 0(smallint),却把1(bit)转换成 -1(smallint),这是一个错误,但是SSIS不会抛出任何异常消息:

在Package中处理 bit column

4,故障排除

产生错误的根本原因是SSIS引擎无法直接把类型DT_BOOL直接转换为类型DT_I2,为了排除这种错误,用户可以使用派生列,通过SSIS表达式显式转换数据类型:

(DT_I2)(IsMerged ?  : )

在Package中处理 bit column

在Package中处理 bit column

三,类型转换的流程

SSIS引擎对数据类型的处理流程是:

  • 对于外部数据组件,不管是用作输入的数据源组件,还是用作输出的数据目的组件,SSIS都会把外部数据组件的数据类型等价转换为SSIS支持的原生数据类型,类型以DT_xxxx命名;
  • 映射关系发生在SSIS支持的原生数据类型之间,因此,数据转换是指 DT_type和DT_type之间的类型转换;

SSIS引擎和外部数据源的类型转换,虽然会有精度的损失,例如,时间,但是,可以视为是等价的,转换的流程是:

  • 在加载数据时,SSIS把外部数据源的数据加载到缓存中,把外部数据源的类型转换为SSIS支持的数据类型;
  • 在输出数据时,SSIS把缓存的数据输出到外部的目标容器中,把SSIS的数据类型转换为外部数据支持的数据类型;

在数据类型的转换过程中,开发人员需要明确知道类型转换的结果,即使SSIS引擎不抛出任何异常,也要复查Package输出的结果,避免出现错误。

忠告:不要在SSIS中做类型转换,如果转换是必需的,那么请使用SSIS表达式做显式的类型转换。

参考文档:

SSIS: True/False and 1/0 values on Bit Columns

上一篇:如何用java获得字符串的ASCII值


下一篇:pymysql实现MySQL与Python交互