你的位置:首页 > 数据库

[数据库]SSIS 数据类型和类型转换


当数据进入package的data flow中时,SSIS从数据源抽取(extract)数据,并转换成SSIS数据类型,SSIS的数据类型主要分为三类:字符(string),数值(numeric)和日期/时间(date/time),如果源数据不能转换成相应的SSIS 数据类型,就会报错。SSIS的数据类型,以“DT_”开头。

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

1,字符类型

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

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

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

2,数值类型

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

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

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

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

3,日期时间类型

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

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

二,类型转换

在SSIS中,能够对数据进行强制类型转换,转换的格式是:(type) expression,在进行类型转换时,尽量使用窄的数据类型,这样能够提高数据传输的速度;但是,数据转换需要付出一定的代价,因此,必须权衡类型转换和数据传输对性能的影响。

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.

1,将字符串转换成TSQL的日期/时间类型,[] 表示可选

  • 转换成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]"

2,转换成字符串

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

  • 将整数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;

3,数值类型转换

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

三,SSIS 变量(Variable)的数据类型

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. Variables can use only a subset of the Integration Services data types. 

1,字符数据类型

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

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

2,数值类型

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

3,日期/时间类型

  • DateTime:对应TSQL的datetime
  • Object:对应TSQL的time,date,datetime2

 

参考文档:

Integration Services (SSIS) Expressions

Cast (SSIS Expression)

Integration Services Data Types

SQL Server Integration Services, Data Type Mapping