如何灵活运用SQL Server 2005 SSIS变量

　　随着sql server 2005不断的推广和普及，越来越多的企业的决策支持项目采用了sql server 2005商务智能解决方案。本文就ssis开发etl（extract—transform—load，数据抽取、转换、装载的过程）过程中ssis用户变量的使用向读者作简单介绍。

　　一、 ssis变量简介

　　ssis（sql server integration services，sql server整合服务）变量分为两种，一种是系统变量，一种用户定义的变量。系统变量包含有关包、容器、任务或事件处理程序的非常有用的信息。例如，在运行时，machinename 系统变量包含运行包的计算机的名称，starttime 变量包含包开始运行的时间。系统变量是只读的。在 ssis 中，用户变量是用户在开发的过程中根据需要申明的变量。用户变量可以使用在脚本中，在由优先约束、for 循环容器、派生列转换和条件性拆分转换使用的表达式中，以及在更新属性值的属性表达式中。

　　在各种编程语言中申明的变量一般情况下都会有作用范围的，ssis变量也不例外，ssis变量也是有作用范围的。根据作用范围分类，变量分为包变量和组件变量。包变量在包任何一个组件中都可以调用，组件变量只能够在申明变量的组件中有效。在变量的窗口中可以看到变量的作用域（如图1所示）。begindate就是testpackage包变量，而i是数据转换这个数据流组件的变量。

　　图1

　　 二、 ssis用户变量的申明、赋值、使用

　　1、 申明变量

　　申明变量非常简单，如果你要申明包变量，只要单击控制流选项卡,然后在包开发区域空白处单击右键选择变量命令（出现如图1的画面），单击新建变量按钮就新建一个变量，输入名称，选择数据类型，赋初值就完成了 (如图2)。

　　红色方框是新建变量按钮，蓝色方框是删除按钮。如果要申明某数据流任务组件使用的变量，只要双击该数据流任务组件，在流控制选项卡空白的地方单击右键选择变量命令，在变量窗口中新建一个变量，这时变量的作用域就是你所选的流任务组件。

　　图2

　　2、 赋值

　　在实际开发中，除了在变量申明的时候给变量赋值外,笔者还摸索了两种方式给变量赋值，一种是通过执行 sql 任务组件返回值的方式给变量赋值，一种是通过脚本组件来给变量赋值。

　　利用执行 sql 任务组件方法是先设置好组件的数据库连接属性，然后输入从数据取数据的sql语句，设置组件返回的结果集为单行。在结果集界面中单击“新建“，在结果集那一列输入你刚才sql 语句中返回列的名称，在变量名称列选择你要赋值的变量（过程如图3、图4所示）。

　　图3

　　图4

　　图3中红色方框中sql语句非常简单，返回单行，结果是1。在图4中，将返回的result列的一行赋值给用户变量i。

　　利用脚本组件赋值变量比较简单，只需要设置脚本组件的readonlyvariable或者readwritevariable,将变量的名称设置为他们的值（多个变量以逗号分隔）,它们的区别是前者在脚本组件只能够读，后者可以读写。然后在脚本组件中通过

dts.variables("i").value = 1

　　3、 变量的使用

　　变量在ssis中使用的地方很多，笔者介绍两个典型的应用。

　　（1） 执行 sql 任务组件的参数

　　假定申明了一个日期类型变量startdate，用户需要通过从某个表中选择在startdate日期之前的数据，这个时候需要将startdate作为参数传给执行 sql 任务组件。在执行 sql 任务组件输入sql的地方输入如下命令语句：

select * from table_a where 日期字段 < ?

　　然后在参数据映射界面新增映射,在变量名称列选择用户变量startdate,选择类型为date,在参数名称列输入给参数取的名称。这样就可以将startdate变量传给sql任务组件的sql语句了。

　　（2） 在脚本组件中赋值

　　可以在脚本组件中通过dts.variables("i").value = 1方式赋值给变量，也可以通过这种方式来使用变量。比如dts.variables("other_variable").value = dts.variables("i").value+1,这个语句是可以在脚本组件中执行的,将i变量加1后赋值给另外一个变量。