变量(Variable)用于存储在Package运行时用到的值,集成服务支持两种类型的变量:用户自定义的变量和系统变量,自定义的变量由用户来定义,系统变量由集成服务来定义。
变量的用途十分广泛,用于容器、Task和事件处理程序中,在Script Task和Scipt Component 也会用变量。SSIS中的变量是强类型的,这意味着必须为变量设置正确的类型。
一,变量的类型
SSIS变量支持的类型列表:
- Boolean:布尔类型,只有两个有效值:true 和 false
- Char:单个字符
- DateTime:日期和时间,格式是:5/29/2020 6:05:48 PM
- DBNull:NULL
- Decimal:精确小数
- Double 和 Single,双精度和单精度浮点数
- String:字符类型
- Object:System.Object类型
- SByte,Int16,Int32,Int64:有符号的整数,可以为正整数、0和负整数
- Byte,Uint32,Uint64:无符号的整数,值必须大于等于0,Byte占用1个字节,相当于UInt8,
最为特殊的是Object类型,该类型的变量可以转换为其他数据类型。这些类型,有些可以和TSQL的数据类型进行匹配,有些不能匹配,在使用时,要根据自己的需要,为变量设置正确的数据类型。
二,变量的值
SSIS的变量不允许为null,当没有为变量赋值时,变量有默认值:
![](https://i-blog.csdnimg.cn/blog_migrate/a982bc96c3305213038e167699ac6e9c.png)
经过测试,string 数据类型的长度可能是8000Bytes,也就是对应SQL Server的varchar(8000) 或 nvarchar(4000)
三,变量的数据类型和TSQL数据类型的映射
SSIS 变量的数据类型,不同于SSIS的数据类型,但都和SSIS的数据类型相兼容,在进行表达式求值时,SSIS自动将变量的数据类型隐式转换成SSIS的数据类型,然后进行求值。
1,字符数据类型
字符变量和TSQL数据类型的映射关系:
- String:char,nchar,varchar(n),nvarchar(n), 最大8000个字节。
- object:varchar(max),nvarchar(max)
- Char: char(1)
2,数值类型
数值类型的变量和TSQL数据类型的映射关系:
- Boolean:bit
- Int64:bigint
- Int32:int
- Int16:smallint
- Byte:tinyint
- object:varbinary(max), varchar(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
4,NULL
DBNull对应TSQL的NULL,这个类型很奇怪,暂时不知道用途。
四,如何处理TSQL中的max类型?
在执行Execute SQL Task时,如果把nvarchar(max) 和 varchar(max) 类型的值赋值给变量,并且变量的类型是String,那么SSIS会抛出错误,错误消息如下:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "strVar": "The type of the value (DBNull) being assigned to variable "User::strVar" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.".
错误消息会误导开发者做出错误的判断,实际上,正确的做法是把变量的类型修改Object 或者把字段的类型修改为nvarchar(n) 或者varchar(n)。
但是,当变量的长度大于等于8000字节时,实际上,无法通过变量来传递到其他组件中。
![](https://i-blog.csdnimg.cn/blog_migrate/a9e2d05f27ab816af2b4c346d731b923.png)
举个例子,在EXECUTE SQL TASK的参数映射中,把Parameter Size 设置为-1,表示不限制变长字符和binary的长度,但是,实际上,当字节数量大于8000时,无法把Object类型的变量转换为NVARCHAR类型。
SSIS会抛出错误消息,从Object变量中抽取数据时出错:
[Execute SQL Task] Error: Executing the query "insert into dbo.TestDT values(?)" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_WSTR)".
从数据类型DBTYPE_WSTR中,可以看出,通过EXECUTE SQL TASK 转换的NVARCHAR 和 -1 (Parameter Size),实际上,最大值是NVARCHAR(4000) 或 VARCHAR(8000)。
参考文档: