• 欢迎访问金笔头博客,这是一个菜鸟(伪)程序员的自留地,欢迎访问我的github:点击进入

SSIS中数据类型及其转换

SSIS eason 7514次浏览 0个评论 扫描二维码

前言

    在SSIS包开发过程中经常会涉及到数据类型转换,然而在编辑表达式的时候你会发现很多数据类型在编辑窗口并没有给出详细的描述,为了更好的了解SSIS和避免以后在项目中犯此类错误,笔者特地整理此篇文章,供大家参考的同时也方便自己日后查阅。
 
下表列出了 SSIS中的 数据类型。

数据类型

说明

DT_BOOL

布尔值。

DT_BYTES

二进制数据值。长度可变,且最大长度为 8000 个字节。

DT_CY

货币值。此数据类型为八字节有符号整数,其小数位数为 4,最大精度为 19。

DT_DATE

由年、月、日、小时、分钟、秒和小数秒组成的日期结构。小数秒的固定小数位数为 7。

DT_DATE 数据类型是使用 8 字节浮点数字来实现的。日以整数增量表示,从 1899 年 12 月 30 日开始,午夜时间为零点。小时值以数字的小数部分的绝对值表示。但是,浮点值无法表示所有实数值;因此,可以在 DT_DATE 中显示的日期的范围受到限制。

另一方面,DT_DBTIMESTAMP 以内部具有单独的年、月、日、小时、分钟、秒和毫秒字段的结构表示。此数据类型对其能够表示的日期有较大的范围限制。

DT_DBDATE

由年、月和日组成的日期结构。

DT_DBTIME

由小时、分钟和秒组成的时间结构。

DT_DBTIME2

由小时、分钟、秒和小数秒组成的时间结构。小数秒的最大小数位数为 7。

DT_DBTIMESTAMP

由年、月、日、小时、分钟、秒和小数秒组成的时间戳结构。小数秒的最大小数位数为 3。

DT_DBTIMESTAMP2

由年、月、日、小时、分钟、秒和小数秒组成的时间戳结构。小数秒的最大小数位数为 7。

DT_DBTIMESTAMPOFFSET

由年、月、日、小时、分钟、秒和小数秒组成的时间戳结构。小数秒的最大小数位数为 7。

与 DT_DBTIMESTAMP 和 DT_DBTIMESTAMP2 数据类型不同,DT_DBTIMESTAMPOFFSET 数据类型具有时区偏移量。此偏移量指定时间相对于协调世界时 (UTC) 偏移的小时和分钟数。系统使用时区偏移量获取本地时间。

时区偏移量必须包括符号(加或减)以表示是用 UTC 加上还是减去偏移量。偏移量的有效小时数介于 -14 和 +14 之间。分钟偏移量的符号取决于小时偏移量的符号:

如果小时偏移量的符号为负,则分钟偏移量必须为负或零。

如果小时偏移量的符号为正,则分钟偏移量必须为正或零。

如果小时偏移量的符号为零,则分钟偏移量可以为 -0.59 到 +0.59 之间的任何值。

DT_DECIMAL

精度和小数位数固定的精确数值。此数据类型为具有单独符号的十二字节无符号整数,其小数位数为 0 到 28,最大精度为 29。

DT_FILETIME

64 位值,表示从 1601 年 1 月 1 日起长度为 100 纳秒的间隔的数量。小数秒的最大小数位数为 3。

DT_GUID

全局唯一标识符 (GUID)。

DT_I1

单字节有符号整数。

DT_I2

双字节有符号整数。

DT_I4

四字节有符号整数。

DT_I8

八字节有符号整数。

DT_NUMERIC

精度和小数位数固定的精确数值。此数据类型为具有单独符号的十六字节无符号整数,其小数位数为 0 到 38,最大精度为 38。

DT_R4

单精度浮点值。

DT_R8

双精度浮点值。

DT_STR

以 null 值结束的 ANSI/MBCS 字符串,最大长度为 8000 个字符。(如果列值包含其他 null 终止符,则字符串将在第一个 Null 值出现的位置截断。)

DT_UI1

单字节无符号整数。

DT_UI2

双字节无符号整数。

DT_UI4

四字节无符号整数。

DT_UI8

八字节无符号整数。

DT_WSTR

以 Null 值结束的 Unicode 字符串,最大长度为 4000 个字符。(如果列值包含其他 Null 终止符,则字符串将在第一个 Null 值出现的位置截断。)

DT_IMAGE

二进制值,最大大小为 231-1 (2,147,483,647) 个字节。.

DT_NTEXT

Unicode 字符串,最大长度为 230-1 (1,073,741,823) 个字符。

DT_TEXT

ANSI/MBCS 字符串,最大长度为 231-1 (2,147,483,647) 个字符。

数据类型的转换

    如果列中的数据不必是源数据类型分配的全角形式,那么最好更改列的数据类型。使每个数据行尽可能窄有助于优化传输数据时的性能,因为每行越窄,数据从源移动到目标就越快

    SSIS 包含一组完整的数值数据类型,这样可以使数据类型近似匹配于数据大小。例如,如果数据类型为 DT_UI8 的列中的值始终为 0 到 3000 之间的整数,则可以将数据类型更改为 DT_UI2。同样,如果数据类型为 DT_CY 的列改用整数数据类型可以满足包数据要求,则可以将数据类型更改为 DT_I4。

可以按以下方法更改列的数据类型:

  1. 使用表达式隐式转换数据类型。
  2. 使用转换运算符转换数据类型。
  3. 使用数据转换将列的数据类型从一种数据类型转换为另一种数据类型。
  4. 使用派生列转换创建数据类型与原始列数据类型不同的列的副本。

以下关系图显示了合法的转换运算。

SSIS中数据类型及其转换

转换为某些数据类型时需要参数。下表列出了这些数据类型及其参数。

数据类型

参数 示例

DT_STR

charcount

codepage

(DT_STR,30,1252) 将 30 个字节(即 30 个单字符)转换为使用 1252 代码页的 DT_STR 数据类型。

DT_WSTR

Charcount

(DT_WSTR,20) 将 20 个字节对(即 20 个 Unicode 字符)转换为 DT_WSTR 数据类型。

DT_BYTES

Bytecount

(DT_BYTES,50) 将 50 个字节的数据转换为 DT_BYTES 数据类型。

DT_DECIMAL

Scale

(DT_DECIMAL,2) 将数值转换为带 2 位小数的 DT_DECIMAL 数据类型。

DT_NUMERIC

Precision

Scale

(DT_NUMERIC,10,3) 将数值转换为带 3 位小数且精度为 10 的 DT_NUMERIC 数据类型。

DT_TEXT

Codepage

(DT_TEXT,1252) 将值转换为使用 1252 代码页的 DT_TEXT 数据类型

字符串和日期/时间数据类型之间的转换

下表列出了日期/时间数据类型和字符串之间的转换结果:

    使用转换运算符或数据转换转换时,日期或时间数据类型将转换为相应的字符串格式。例如,DT_DBTIME 数据类型将转换为格式为“hh:mm:ss”的字符串。

    要从字符串转换为日期或时间数据类型时,该字符串必须使用与相应的日期或时间数据类型对应的字符串格式例如,若要将某些日期字符串成功转换为 DT_DBDATE 数据类型,这些日期字符串的格式必须为“yyyy-mm-dd”。

数据类型

字符串格式

DT_DBDATE

yyyy-mm-dd

DT_FILETIME

yyyy-mm-dd hh:mm:ss:fff

DT_DBTIME

hh:mm:ss

DT_DBTIME2

hh:mm:ss[.fffffff]

DT_DBTIMESTAMP

yyyy-mm-dd hh:mm:ss[.fff]

DT_DBTIMESTAMP2

yyyy-mm-dd hh:mm:ss[.fffffff]

DT_DBTIMESTAMPOFFSET

yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm]

在 DT_FILETIME 和 DT_DBTIMESTAMP 的格式中,fff 是一个表示小数秒的介于 0 和 999 之间的值。

在 DT_DBTIMESTAMP2、DT_DBTIME2 和 DT_DBTIMESTAMPOFFSET 的日期格式中,fffffff 是一个表示小数秒的介于 0 和 9999999 之间的值。

DT_DBTIMESTAMPOFFSET 日期格式中还包括一个时区元素。在时间元素和时区元素之间有一个空格。

SSIS 数据转换表达式示例

以下示例将一个数值转换为整数。

(DT_I4) 3.57

以下示例将整数转换为使用 1252 代码页的字符串。

(DT_STR,1,1252)5

以下示例将三字符字符串转换为双字节字符。

(DT_WSTR,3)"Cat"

以下示例将整数转换为带两位小数的十进制数。

(DT_DECIMAl,2)500

此示例将整数转换为带 3 位小数且精度为 7 的数值。

(DT_NUMERIC,7,3)4000

以下示例将 FirstName 列中的值(定义为 nvarchar 数据类型且长度 50)转换为使用 1252 代码页的字符串。

(DT_STR,50,1252)FirstName

本示例将类型为 DT_DBDATE 的 DateFirstPurchase 列的值转换为长度为 20 的Unicode 字符串。

(DT_WSTR,20)DateFirstPurchase

以下示例将字符串文字“True”转换为布尔值。

(DT_BOOL)"True"

下面的示例将字符串文字转换为 DT_DBDATE。

(DT_DBDATE) "1999-10-11"

下面的示例将字符串文字转换为使用 5 位小数秒的 DT_DBTIME2 数据类型。(对于 DT_DBTIME2 数据类型可以指定 0 到 7 位小数秒。)

(DT_DBTIME2, 5) "16:34:52.12345"

下面的示例将字符串文字转换为使用 4 位小数秒的 DT_DBTIMESTAMP2 数据类型。(对于 DT_DBTIMESTAMP2 数据类型可以指定 0 到 7 位小数秒。)

(DT_DBTIMESTAMP2, 4) "1999-10-11 16:34:52.1234"

下面的示例将字符串文字转换为使用 7 位小数秒的 DT_DBTIMESTAMPOFFSET 数据类型。(对于 DT_DBTIMESTAMPOFFSET 数据类型可以指定 0 到 7 位小数秒。)

(DT_DBTIMESTAMPOFFSET, 7) "1999-10-11 16:34:52.1234567 + 5:35"

金笔头博客, 版权所有丨如未注明 , 均为原创, 转载请注明SSIS中数据类型及其转换
喜欢 (5)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址