SSIS使用默认值,www.9159.com在Task中需要使用不同的

作者: www.9159.com  发布:2019-11-03

在进行ETL开发时,数据类型(Data Type)是最基础的,但也容易被忽略,楼主使用的SQL Server 版本是2012,用此博文记录,常用的SSIS数据类型和TSQL数据类型的映射。SSIS的数据类型,是指数据流组件使用的数据类型和变量的数据类型(Data Flow 和 Variable)。

不同于SQL Server中NULL表示值是未知的(Unknown Value),没有数据类型,但是,在SSIS中,NULL是有数据类型的,要获取某一个NULL值,必须指定数据类型,例如,变量 Int32的“NULL值”是 NULL(DT_I4),默认值是0。对于变量和参数,不允许有NULL值,因此,NULL值是指默认值,如果变量或参数的值未被显式设置时,SSIS使用默认值。对于Data Flow组件,SSIS保持源数据的NULL值。

Execute SQL Task能够执行带参数的SQL查询语句或存储过程(SP),通过SSIS的变量(Variable)对参数赋值。对于不同的Connection Manager,在Task中需要使用不同的符号(Parameter marker)来标记一个参数,并且在Parameter Mapping中设置参数名字(Parameter Name)。

当数据进入Package的data flow task中时,SSIS 通过数据源组件从数据源抽取(extract)数据,获取元数据类型,并转换成SSIS支持的数据类型,SSIS的数据类型主要分为三类:字符(string),数值(numeric)和日期/时间(date/time),如果源数据类似不能转换成相应的SSIS 数据类型,SSIS Engine就会报错。SSIS的数据类型,以“DT_”开头,是Data Type的简写。

一,Variable和Parameter的默认值

在Execute SQL Task Editor中,设置Parameter Mapping的界面如下:

一,SSIS 数据流的数据类型和TSQL数据类型的映射

在SSIS中,变量不能被设置为NULL,在缺少值时,每种数据类型的变量都会使用默认值:

  • Variable Name:变量的名字,变量的作用域分为UserSystem,使用 :: 来引用作用域中的变量
  • Direction:参数的方向,分为输入参数(Input),输出参数(Output)和返回值(Returnvalue)
  • Data Type:参数的Data Type,必须和变量(Variable)的数据类型相兼容
  • Parameter Name:参数名字,不同的Connection Manager,其值不同,对于OLEDB Connection Manager,使用0,1,2等数值表示第一个,第二个参数,第三个参数等。
  • Parameter Size:默认值是-1,表示让SSIS确定参数的长度。如果参数的数据类型是变长的(varchar或varbinary),必须设置参数的长度,为参数值分配足够长度的空间。

1,字符类型

  • 字符类型的默认值是空字符“”,数据类型是DT_WSTR;
  • 数值类型的默认值是0;
  • Boolean类型的默认值是False;
  • DateTime的默认值是 “12/30/1899 12:00:00 AM”;

www.9159.com 1

字符类型用于存储字符串,在SQL Server中,使用单引号表示一个字符,但是在SSIS中,使用双引号表示一个字符串。

例如,变量varIntNull的Data Type是Int32,使用Expression:NULL(DT_I4) 为其赋值,则该变量的默认值是0。

在Parameter Mapping中,Parameter Marker和Name受到Connection Type的影响,如下表:

SSIS的字符类型和TSQL的数据类型的对应关系:

如果需要测试变量是否缺失值,可以将变量的值和变量数据类型对应的默认值进行比较,

Connection type

  • DT_STR:对应TSQL的 varchar, char
  • DT_WSTR:对应TSQL的 nchar, nvarchar, xml

例如:@[User::MyStringVar]=="",如果表达式返回True,说明变量缺失值;如果表达式返回False,说明变量有值。

Parameter marker

2,数值类型

二,在Data Flow Task中处理NULL值

Parameter name

数值类型分为整数和小数,SSIS的整数类型和TSQL数据类型的对应关系:

在Data Flow中,NULL有Data Type,必须使用NULL(Data_Type)来获取某个Data Type的NULL。要确定某个值是不是NULL,使用ISNULL(Data_Column),这个函数返回值是boolean,如果返回值是True,说明该数据是NULL。如果当ISNULL(Data_Column)返回true时,替换该数据,可以使用如下语句实现TSQL Isnull 函数相同的功能:ISNULL(Data_Column)? DEFAULT_VALUE : Data_Column

Example SQL command

  • DT_BOOL:bit
  • DT_UI1:tinyint,占用一个字节,非负整数,数值范围是:0-255
  • DT_www.9159.com,I2:smallint,占用2个字节,有符号整数
  • DT_I4:int,占用4个字节,有符号整数
  • DT_I8:bigint,占用8个字节,有符号整数
  • DT_BYTES:binary, varbinary, RowVersion

例如,列City是varchar(10),当该列为null时,将其替换为字符串"Unknown",在Derived Column转换组件中,增加一个新列,设置Expression是:

ADO

TSQL的小数数值类型分为两类:精确小数(decimal)和近似小数(float),小数也叫实数(real),SSIS的小数类型和TSQL数据类型的对应关系:

(DT_STR,10,1252)(ISNULL(City) ? "Unknown" : City) 

?

  • DT_NUMERIC:精确小数,decimal
  • DT_R4:近似小数,float(24)
  • DT_R8:近似小数,float(53)

www.9159.com 2

Param1, Param2, …

3,日期时间类型

三,故障排除

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

SSIS的日期时间类型和TSQL数据类型的对应关系:

楼主曾经遇到一个错误:在向OLE DB Destination的中Columns[FullName]插入数据时,违反了完整性约束。

ADO.NET

  • DT_DBDATE:date
  • DT_DBTIME2:time(p)
  • DT_DBTIMESTAMP:datetime
  • DT_DBTIMESTAMP2:datetime2

[OLE DB Destination [12]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[FullName] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value violated the integrity constraints for the column.".

@<parameter name>

SSIS 内置函数:GETDATE() 和 GETUTCDATE() 返回值的数据类型是DT_DBTIMESTAMP,对应TSQL的DateTime,因此,只保留3位毫秒。在Expression Builder中,将时间类型转换成字符串类型,显示的毫秒数有效数值只有3位,末尾补6个0,共9位:

Integrity Constraint包括:域完整性约束,实体完整性和参考完整性,Columns[FullName] 不具有实体完整性约束(PK) 和参考完整性约束(FK),因此,违反的是域完整性约束。域完整性约束包括:CHECK Constraint,DEFAULT Constraint、NOT NULL Constraint(非空约束),Unique Constraint,排除 check,default,unique约束,违反的Integrity Constraint 就是 Not Null Constraint,即,违反Target Column不能为NULL 的约束。

@<parameter name>

  • (DT_WSTR,30) GETDATE(),Evaluated Value是:2016-10-13 17:04:01.765000000
  • (DT_DBTIMESTAMP2,7) GETDATE(),Evaluated Value是:10/13/2016 5:01:54 PM

Data Flow组件不会处理数据列的nullability,错误的Root Cause是OLE DB Source Component 返回的列FullName 存在Null值,但是在OLE DB Destination 组件中,Target Table的列FullName不允许插入Null值,因此,违反了非NULL约束。SSIS在调用 bulk insert 命令插入数据时,SQL Server 检测到数据插入操作违反了表的nullability,停止事务的运行,并向SSIS 报错。

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID

二,SSIS 变量(Variable)的数据类型和TSQL数据类型的映射**

解决方法:

ODBC

SSIS 变量的数据类型,不同于SSIS的数据类型,但都和SSIS的数据类型相兼容,在进行表达式求值时,SSIS自动将变量的数据类型隐式转换成SSIS的数据类型,然后进行求值。

  1. 在数据源中修改,在数据源中,将NULL值替换成默认值;推荐做法,避免NULL值进入Data Flow;
  2. 在数据流中使用Derived Column转换组件,检测到NULL值后,将其替换成默认值;
  3. 修改Target Table Column的Nullability,使该列允许为NULL

?

Variables have a Variant data type and the expression evaluator converts the data type of a variable from a Variant subtype to an Integration Services data type before it evaluates the expression. 

 

1, 2, 3, …

1,字符数据类型

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

字符变量和TSQL数据类型的映射关系:

EXCEL and OLE DB

  • String:char,nchar,varchar(n),nvarchar(n)
  • object:varchar(max),nvarchar(max)

?

2,数值类型

0, 1, 2, 3, …

数值类型的变量和TSQL数据类型的映射关系:

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

  • Boolean:bit
  • Int64:bigint
  • Int32:int
  • Int16:smallint
  • Byte:tinyint
  • object:binary, varbinary(n), varbinary(max)
  • 精确小数:Decimal 在SQL Server 2012以后,对应TSQL的decimal
  • 近似小数:Single 对应TSQL的float(24),  Double 对应TSQL的float(53)

1,执行SQL查询语句

3,日期/时间类型

楼主在实际开发中,最常用的Connection Type是OLEDB,设置Task的ConnectionType为OLE DB,在SQLStatement属性中使用 ? 来标记一个参数,? 叫做参数标记(Parameter Marker)。

日期/时间类型的变量和TSQL数据类型的映射关系:

select ID,c1
from dbo.dt_test
where c1=?
  • DateTime:对应TSQL的datetime
  • Object:对应TSQL的time,date,datetime2

www.9159.com 3

三,强制类型转换

使用SSIS 变量(Variable)为参数赋值,Parameter Name必须是0,1,2等数字

SSIS在进行表达式求值时,自动将一个数据类型隐式转换成相兼容的另外一个数据类型,如果类型不兼容,必须强制类型转换,否则,SSIS报错。对数据进行强制类型转换的格式是:(type) expression,在进行显式类型转换时,尽量使用窄的数据类型,这样能够提高数据传输的速度;但是,数据转换需要付出一定的代价,因此,必须权衡类型转换和数据传输对性能的影响。

www.9159.com 4

An implicit conversion of a data type occurs when the expression evaluator automatically converts the data from one data type to another. If the data in a column does not require the full width allocated by the source data type, you might want to change the data type of the column. Making each data row as narrow as possible helps optimize performance when transferring data because the narrower each row is, the faster the data is moved from source to destination.

2,执行SP,设置参数的方向

1,将字符串转换成TSQL的日期/时间类型

使用Execute SQL Task执行SP时,参数映射的Direction属性使用Input,Output和Returnvalue 分别表示:输入参数,输出参数和SP的返回值

在SSIS中,字符串常量使用双引号“”,[] 表示可选:

declare @return_value int 
declare @output_var int 
declare @input_var int
set @input_var=1

exec @return_value=dbo.usp_test @para1=@input_var, @para2= @output_var output;
  • 转换成date:(DT_DBDATE)"yyyy-mm-dd"
  • 转换成time(n):(DT_DBTIME2,n)"hh:mm:ss[.fffffff]"
  • 转换成datetime:(DT_DBTIMESTAMP)"yyyy-mm-dd hh:mm:ss[.fff]"
  • 转换成datetime2(n):(DT_DBTIMESTAMP2,n)"yyyy-mm-dd hh:mm:ss[.fffffff]"

使用OLEDB Connection Manager,使用 ? 表示一个参数,如图

2,转换成字符串

www.9159.com 5

字符串分为双字节字符和单字节字符,对于单字节字符,SSIS使用 DT_STR 表示,在强制类型转换时,必须制定code page和字符长度:

在Parameter Mapping Tab中设置参数映射:

  • 将整数5转换为单字节字符:(DT_STR,30,1252)5
  • 将整数5转换为双字节字符:(DT_WSTR,30)5
  • 将 DT_DBTIMESTAMP 类型转换成字符串:(DT_WSTR,30)GETDATE(),返回的数据格式是: 2016-10-13 14:55:31.248000000,GETDATE()返回的数据类型是DT_DBTIMESTAMP;
  • 第一个参数是SP的返回值,Direction 选择 Returnvalue,Parameter Name 是 0;
  • 第二个参数是SP的输入参数,Direction 选择 Input,Parameter Name 是 1;
  • 第三个参数是SP的输出参数,Direction 选择 Output,Parameter Name 是 2;

3,数值类型转换

 www.9159.com 6

  • 将字符串转换成bit:(DT_BOOL)"True"
  • 将小数转换成int:(DT_I4) 3.57
  • 将整数转化成精确小数:(DT_NUMERIC,7,3)4000

3,设置参数的Data Type

四,数据类型转换的性能

楼主整理的参数的Data Type和TSQL 数据类型的映射关系

将数据从一个SQL Server 加载到另一个SQL Server之前,如果需要转换数据类型,建议使用TSQL Conversion,这样,能简化Package的设计,提高转换速度。

3.1,数值类型

www.9159.com 7

  • BYTE:映射 SSIS 的 DBTYPE_UI1,映射 TSQL 的 TinyInt
  • SHORT:映射 SSIS 的 DBTYPE_I2, 映射 TSQL 的 smallint
  • LONG:映射 SSIS 的 DBType_I8,映射 TSQL 的 bigint
  • FLOAT: 映射 TSQL 的 float(24)
  • DOUBLE:映射 TSQL 的 float(53)
  • DECIMAL: 映射 TSQL 的 decimal

五,参数的数据类型

3.2,日期/时间类型

在Execute SQL Task引用变量时,必须在Parameter Mapping Tab中设置参数的Data Type,请参考《Execute SQL Task 参数和变量的映射》

  • DATE:映射TSQL的DateTime2(7)

 

注意:如果SSIS 变量的Data Type是DateTime,那么参数的Data Type应使用 DATE,但是,数据类型为DateTime的变量,只保留到秒,毫秒位是0.

参考文档:

3.3,字符串类型

Integration Services (SSIS) Expressions.aspx)

  • VARCHAR: 映射TSQL的varchar
  • NVARCHAR: 映射TSQL的nvarchar

Cast (SSIS Expression).aspx)

4,在做增量更新时,发现导入的数据量少于源数据

Integration Services Data Types.aspx)

楼主在调试SSIS Package时,使用ModifiedDate字段做增量更新,Package中使用Execute SQL Task获取数据源中DataUpdateTime字段的最大值,并将该值赋值给变量:User::MaxLastModifiedDate,Package运行成功,但是导入的数据量少于源数据;通过测试,发现DateTime类型的变量,其时间部分只保留到秒,而不会计算毫秒部分,导致导入的数据量少于源数据。

SQL Server Integration Services, Data Type Mapping

调试SSIS Package,下断点(breakpoint),打开Watch Tab,查看变量运行时的值:

Performance Comparison between Data Type Conversion Techniques in SSIS 2008

www.9159.com 8

这两个变量定义为DateTime类型,经过测试,如果变量定义成String类型,实际上是一样的,时间只会精确到秒:

www.9159.com 9

然而,数据源中的DataUpdateTime使用的Datetime2(7),精度十分高:

www.9159.com 10

发生这种问题的根源是 SSIS的数据类型和SQL Server的数据类型不是一一对应的,存在差异。为了避免这种问题,可以对MaxLastModifiedDate 变量加1s。

在使用OLEDB数据源导入数据时,使用如下的Where条件,就能把所有的数据都导入到DW中。

where DataUpdatedOn > ?    --MinLastModifiedDate
and DataUpdatedOn<=?        --MaxLastModifiedDate

注意:SSIS 变量的日期和时间类型只保留到秒,而数据库中的时间可以保留到毫秒位(1-7位毫秒数)

 

参考文档:

SQL Server SP2 – What’s new for SSIS

Map Query Parameters to Variables in an Execute SQL Task

Parameters and Return Codes in the Execute SQL Task

本文由9159.com发布于www.9159.com,转载请注明出处:SSIS使用默认值,www.9159.com在Task中需要使用不同的

关键词: