逻辑类型是最常用的数据类型之一,一般编程语言,例如,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)
二,探究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源的连接管理器:
点击按钮“Preview...”,看到IsMerged 字段的值不是0 或1, 而是被SSIS转换成True 或False:
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类型:
从映射关系中,可以看出,转换实际上发生在类型DT_BOOL和 类型DT_I2之间,即是SSIS支持的原生类型之间的相互转换,虽然DT_BOOL和 DT_I2之间的转换是合法的,SSIS引擎不会抛出任何异常,但是转换的结果是不等价的。
3,查看插入的结果
从SQL Server数据库中查看插入的结果,当把外部数据源的Bit类型的数据转换为smallint时,SSIS 把0(bit)转变成 0(smallint),却把1(bit)转换成 -1(smallint),这是一个错误,但是SSIS不会抛出任何异常消息:
4,故障排除
产生错误的根本原因是SSIS引擎无法直接把类型DT_BOOL直接转换为类型DT_I2,为了排除这种错误,用户可以使用派生列,通过SSIS表达式显式转换数据类型:
(DT_I2)(IsMerged ? : )
三,类型转换的流程
SSIS引擎对数据类型的处理流程是:
- 对于外部数据组件,不管是用作输入的数据源组件,还是用作输出的数据目的组件,SSIS都会把外部数据组件的数据类型等价转换为SSIS支持的原生数据类型,类型以DT_xxxx命名;
- 映射关系发生在SSIS支持的原生数据类型之间,因此,数据转换是指 DT_type和DT_type之间的类型转换;
SSIS引擎和外部数据源的类型转换,虽然会有精度的损失,例如,时间,但是,可以视为是等价的,转换的流程是:
- 在加载数据时,SSIS把外部数据源的数据加载到缓存中,把外部数据源的类型转换为SSIS支持的数据类型;
- 在输出数据时,SSIS把缓存的数据输出到外部的目标容器中,把SSIS的数据类型转换为外部数据支持的数据类型;
在数据类型的转换过程中,开发人员需要明确知道类型转换的结果,即使SSIS引擎不抛出任何异常,也要复查Package输出的结果,避免出现错误。
忠告:不要在SSIS中做类型转换,如果转换是必需的,那么请使用SSIS表达式做显式的类型转换。
参考文档: