SSIS 數據類型和類型轉換

在進行ETL開發時，數據類型（Data Type）是最基礎的，但也容易被忽略，樓主使用的SQL Server 版本是2012，用此博文記錄，常用的SSIS數據類型和TSQL數據類型的映射關係。SSIS的數據類型，主要是指數據流（Data Flow）數據類型和變數（Variable）數據類型。

當數據進入Package的data flow task中時，SSIS 通過數據源組件從數據源抽取（extract）數據，獲取元數據類型，並轉換成SSIS支持的數據類型，SSIS的數據類型主要分為三類：字元（string），數值（numeric）和日期/時間（date/time），如果源數據類似不能轉換成相應的SSIS 數據類型，SSIS Engine就會報錯。SSIS的數據類型，以“DT_”開頭，是Data Type的簡寫。

一，SSIS 數據流的數據類型和TSQL數據類型的映射

1，字元類型

字元類型用於存儲字元串，在SQL Server中，使用單引號表示一個字元，但是在SSIS中，使用雙引號表示一個字元串。

SSIS的字元類型和TSQL的數據類型的對應關係：

• DT_STR：對應TSQL的 varchar, char
• DT_WSTR：對應TSQL的 nchar, nvarchar, sql_variant, xml

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），小數也叫實數（real），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 內置函數：GETDATE() 和 GETUTCDATE() 返回值的數據類型是DT_DBTIMESTAMP，對應TSQL的DateTime，因此，只保留3位毫秒。在Expression Builder中，將時間類型轉換成字元串類型，顯示的毫秒數有效數值只有3位，末尾補6個0，共9位：

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

二，強制類型轉換

SSIS在進行表達式求值時，自動將一個數據類型隱式轉換成相相容的另外一個數據類型，如果類型不相容，必須強制類型轉換，否則，SSIS報錯。對數據進行強制類型轉換的格式是：(type) expression，在進行顯式類型轉換時，儘量使用窄的數據類型，這樣能夠提高數據傳輸的速度；但是，數據轉換需要付出一定的代價，因此，必須權衡類型轉換和數據傳輸對性能的影響。

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.

1，將字元串轉換成TSQL的日期/時間類型

在SSIS中，字元串常量使用雙引號“”，[] 表示可選：

• 轉換成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)的數據類型和TSQL數據類型的映射

SSIS 變數的數據類型，不同於SSIS的數據類型，但都和SSIS的數據類型相相容，在進行表達式求值時，SSIS自動將變數的數據類型隱式轉換成SSIS的數據類型，然後進行求值。

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，字元數據類型

字元變數和TSQL數據類型的映射關係：

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

2，數值類型

數值類型的變數和TSQL數據類型的映射關係：

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

3，日期/時間類型

日期/時間類型的變數和TSQL數據類型的映射關係：

• 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