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, 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, RowVersion

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 變量(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)

數值類型的變量和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

三，強制類型轉換

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

四，資料類型轉換的性能

将資料從一個SQL Server 加載到另一個SQL Server之前，如果需要轉換資料類型，建議使用TSQL Conversion，這樣，能簡化Package的設計，提高轉換速度。

五，參數的資料類型

參考文檔：

<a href="https://msdn.microsoft.com/en-us/library/ms137547(v=sql.110).aspx" target="_blank">Integration Services (SSIS) Expressions</a>

<a href="https://msdn.microsoft.com/en-sg/library/ms141704(v=sql.110).aspx" target="_blank">Cast (SSIS Expression)</a>

<a href="https://msdn.microsoft.com/en-us/library/ms141036(v=sql.110).aspx" target="_blank">Integration Services Data Types</a>

<a href="http://milambda.blogspot.sg/2014/02/sql-server-integration-services-data.html" target="_blank">SQL Server Integration Services, Data Type Mapping</a>

<a href="https://blogs.msdn.microsoft.com/sqlcat/2009/12/01/performance-comparison-between-data-type-conversion-techniques-in-ssis-2008/" target="_blank">Performance Comparison between Data Type Conversion Techniques in SSIS 2008</a>

本文版權歸作者和部落格園所有，歡迎轉載，但未經作者同意，必須保留此段聲明，且在文章頁面醒目位置顯示原文連接配接，否則保留追究法律責任的權利。

本文轉自悅光陰部落格園部落格，原文連結：http://www.cnblogs.com/ljhdo/p/5174681.html，如需轉載請自行聯系原作者