在進(jìn)行ETL開發(fā)時(shí)，數(shù)據(jù)類型（Data Type）是最基礎(chǔ)的，但也容易被忽略，樓主使用的SQL Server 版本是2012，用此博文記錄，常用的SSIS數(shù)據(jù)類型和TSQL數(shù)據(jù)類型的映射。SSIS的數(shù)據(jù)類型，是指數(shù)據(jù)流組件使用的數(shù)據(jù)類型和變量的數(shù)據(jù)類型（Data Flow 和 Variable）。

當(dāng)數(shù)據(jù)進(jìn)入Package的data flow task中時(shí)，SSIS 通過數(shù)據(jù)源組件從數(shù)據(jù)源抽?。╡xtract）數(shù)據(jù)，獲取元數(shù)據(jù)類型，并轉(zhuǎn)換成SSIS支持的數(shù)據(jù)類型，SSIS的數(shù)據(jù)類型主要分為三類：字符（string），數(shù)值（numeric）和日期/時(shí)間（date/time），如果源數(shù)據(jù)類似不能轉(zhuǎn)換成相應(yīng)的SSIS 數(shù)據(jù)類型，SSIS Engine就會報(bào)錯(cuò)。SSIS的數(shù)據(jù)類型，以“DT_”開頭，是Data Type的簡寫。

一，SSIS 數(shù)據(jù)流的數(shù)據(jù)類型和TSQL數(shù)據(jù)類型的映射

1，字符類型

字符類型用于存儲字符串，在SQL Server中，使用單引號表示一個(gè)字符，但是在SSIS中，使用雙引號表示一個(gè)字符串。

SSIS的字符類型和TSQL的數(shù)據(jù)類型的對應(yīng)關(guān)系：

  • DT_STR：對應(yīng)TSQL的 varchar, char

  • DT_WSTR：對應(yīng)TSQL的 nchar, nvarchar, xml

2，數(shù)值類型

數(shù)值類型分為整數(shù)和小數(shù)，SSIS的整數(shù)類型和TSQL數(shù)據(jù)類型的對應(yīng)關(guān)系：

  • DT_BOOL：bit

  • DT_UI1：tinyint，占用一個(gè)字節(jié)，非負(fù)整數(shù)，數(shù)值范圍是：0-255

  • DT_I2：smallint，占用2個(gè)字節(jié)，有符號整數(shù)

  • DT_I4：int，占用4個(gè)字節(jié)，有符號整數(shù)

  • DT_I8：bigint，占用8個(gè)字節(jié)，有符號整數(shù)

  • DT_BYTES：binary, varbinary, RowVersion

TSQL的小數(shù)數(shù)值類型分為兩類：精確小數(shù)（decimal）和近似小數(shù)（float），小數(shù)也叫實(shí)數(shù)（real），SSIS的小數(shù)類型和TSQL數(shù)據(jù)類型的對應(yīng)關(guān)系：

  • DT_NUMERIC：精確小數(shù)，decimal

  • DT_R4：近似小數(shù)，float(24)

  • DT_R8：近似小數(shù)，float(53)

3，日期時(shí)間類型

SSIS的日期時(shí)間類型和TSQL數(shù)據(jù)類型的對應(yīng)關(guān)系：

  • DT_DBDATE：date

  • DT_DBTIME2：time(p)

  • DT_DBTIMESTAMP：datetime

  • DT_DBTIMESTAMP2：datetime2

SSIS 內(nèi)置函數(shù)：GETDATE() 和 GETUTCDATE() 返回值的數(shù)據(jù)類型是DT_DBTIMESTAMP，對應(yīng)TSQL的DateTime，因此，只保留3位毫秒。在Expression Builder中，將時(shí)間類型轉(zhuǎn)換成字符串類型，顯示的毫秒數(shù)有效數(shù)值只有3位，末尾補(bǔ)6個(gè)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)的數(shù)據(jù)類型和TSQL數(shù)據(jù)類型的映射

SSIS 變量的數(shù)據(jù)類型，不同于SSIS的數(shù)據(jù)類型，但都和SSIS的數(shù)據(jù)類型相兼容，在進(jìn)行表達(dá)式求值時(shí)，SSIS自動(dòng)將變量的數(shù)據(jù)類型隱式轉(zhuǎn)換成SSIS的數(shù)據(jù)類型，然后進(jìn)行求值。

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，字符數(shù)據(jù)類型

字符變量和TSQL數(shù)據(jù)類型的映射關(guān)系：

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

  • object：varchar(max),nvarchar(max)

2，數(shù)值類型

數(shù)值類型的變量和TSQL數(shù)據(jù)類型的映射關(guān)系：

  • Boolean：bit

  • Int64：bigint

  • Int32：int

  • Int16：smallint

  • Byte：tinyint

  • object：binary, varbinary(n), varbinary(max)

  • 精確小數(shù)：Decimal 在SQL Server 2012以后，對應(yīng)TSQL的decimal

  • 近似小數(shù)：Single 對應(yīng)TSQL的float(24),  Double 對應(yīng)TSQL的float(53)

3，日期/時(shí)間類型

日期/時(shí)間類型的變量和TSQL數(shù)據(jù)類型的映射關(guān)系：

  • DateTime：對應(yīng)TSQL的datetime

  • Object：對應(yīng)TSQL的time，date，datetime2

三，強(qiáng)制類型轉(zhuǎn)換

SSIS在進(jìn)行表達(dá)式求值時(shí)，自動(dòng)將一個(gè)數(shù)據(jù)類型隱式轉(zhuǎn)換成相兼容的另外一個(gè)數(shù)據(jù)類型，如果類型不兼容，必須強(qiáng)制類型轉(zhuǎn)換，否則，SSIS報(bào)錯(cuò)。對數(shù)據(jù)進(jìn)行強(qiáng)制類型轉(zhuǎn)換的格式是：(type) expression，在進(jìn)行顯式類型轉(zhuǎn)換時(shí)，盡量使用窄的數(shù)據(jù)類型，這樣能夠提高數(shù)據(jù)傳輸?shù)乃俣?；但是，?shù)據(jù)轉(zhuǎn)換需要付出一定的代價(jià)，因此，必須權(quán)衡類型轉(zhuǎn)換和數(shù)據(jù)傳輸對性能的影響。

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，將字符串轉(zhuǎn)換成TSQL的日期/時(shí)間類型

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

  • 轉(zhuǎn)換成date：(DT_DBDATE)"yyyy-mm-dd"

  • 轉(zhuǎn)換成time(n)：(DT_DBTIME2,n)"hh:mm:ss[.fffffff]"

  • 轉(zhuǎn)換成datetime：(DT_DBTIMESTAMP)"yyyy-mm-dd hh:mm:ss[.fff]"

  • 轉(zhuǎn)換成datetime2(n)：(DT_DBTIMESTAMP2,n)"yyyy-mm-dd hh:mm:ss[.fffffff]"

2，轉(zhuǎn)換成字符串

字符串分為雙字節(jié)字符和單字節(jié)字符，對于單字節(jié)字符，SSIS使用 DT_STR 表示，在強(qiáng)制類型轉(zhuǎn)換時(shí)，必須制定code page和字符長度：

  • 將整數(shù)5轉(zhuǎn)換為單字節(jié)字符：(DT_STR,30,1252)5

  • 將整數(shù)5轉(zhuǎn)換為雙字節(jié)字符：(DT_WSTR,30)5

  • 將 DT_DBTIMESTAMP 類型轉(zhuǎn)換成字符串：(DT_WSTR,30)GETDATE()，返回的數(shù)據(jù)格式是: 2016-10-13 14:55:31.248000000,GETDATE()返回的數(shù)據(jù)類型是DT_DBTIMESTAMP；

3，數(shù)值類型轉(zhuǎn)換

  • 將字符串轉(zhuǎn)換成bit：(DT_BOOL)"True"

  • 將小數(shù)轉(zhuǎn)換成int：(DT_I4) 3.57

  • 將整數(shù)轉(zhuǎn)化成精確小數(shù)：(DT_NUMERIC,7,3)4000

四，數(shù)據(jù)類型轉(zhuǎn)換的性能

將數(shù)據(jù)從一個(gè)SQL Server 加載到另一個(gè)SQL Server之前，如果需要轉(zhuǎn)換數(shù)據(jù)類型，建議使用TSQL Conversion，這樣，能簡化Package的設(shè)計(jì)，提高轉(zhuǎn)換速度。

iOS培訓(xùn),Swift培訓(xùn),蘋果開發(fā)培訓(xùn),移動(dòng)開發(fā)培訓(xùn)

五，參數(shù)的數(shù)據(jù)類型

在Execute SQL Task引用變量時(shí)，必須在Parameter Mapping Tab中設(shè)置參數(shù)的Data Type，請參考《Execute SQL Task 參數(shù)和變量的映射》