变量在SSIS包中的使用

2010~2011年经常使用SSIS包采集加工数据,后来换了工作就很少使用。
最近又开始用那玩意采集数据,努力回想之前是怎样操作的,网上各种找各种纠结。趁这次使用记录下日常操作步骤,以备以后不时之需。


--环境SQL Server2012、VS2010(安装数据库时默认会安装)
下载示例数据库AdventureWorks2012,还原数据库并创建目标表。

--还原数据库
CREATE DATABASE [AdventureWorks2012] ON
(FILENAME=N'D:\SQL2012\MSSQL11.SQL12\MSSQL\DATA\AdventureWorks2012_Data.mdf')
FOR ATTACH
GO
USE [DBA_Monitor]
GO
--时间段配置表
CREATE TABLE [dbo].[SSIS_Configuration](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL
) ON [PRIMARY]
GO
--OLE DB目标表
CREATE TABLE [dbo].[SSIS_Product](
    [ProductID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [ProductNumber] [nvarchar](25) NOT NULL,
    [Color] [nvarchar](15) NULL,
    [ListPrice] [money] NOT NULL,
    [SellStartDate] [datetime] NULL
) ON [PRIMARY]
GO
--插入时间段配置数据
INSERT INTO [dbo].[SSIS_Configuration](StartDate,EndDate)
SELECT '20050101','20060101'
UNION ALL
SELECT '20060101','20070101'
UNION ALL
SELECT '20070101','20080101'
GO
View Code

一、变量的使用
1、新建一个Integration Services 项目SSISForVariable
2、创建变量
菜单栏->SSIS->变量(或控制流空白区域右击->变量)调出变量窗口,创建两个日期变量
3、创建连接管理器
新建两个OLE DB连接AdventureWorks2012、DBA_Monitor

4、添加数据流任务->添加OLEDB源、OLEDB目标
编辑OLEDB源配置如图所示,点击参数,修改参数与变量的映射关系

编辑OLEDB目标,调整输入列与输出列的映射关系

5、运行包,查询目标表记录

6、将上述OLEDB源中的SQL命令封装成存储过程,代码如下:

1 use [AdventureWorks2012]
2 create proc pro_GetProduct
3 @StartDate datetime,
4 @EndDate datetime
5 as
6 select ProductID,Name,ProductNumber,Color,ListPrice from Production.Product
7 where SellStartDate>@StartDate and SellStartDate<@EndDate
View Code

执行存储过程得到结果如下:

将OLEDB源中的SQL命令修改成存储过程形式,运行失败!

修改参数名字为存储过程所定义的参数(必须保持完全一致,包括大小写,变量数据类型与存储过程中的保持一致),运行通过。

上面的过程在申明变量的时候就给变量赋值。还可以用另外两种方式给变量赋值,一种是通过执行 SQL 任务组件返回值的方式给变量赋值,一种是通过脚本组件来给变量赋值。
二、变量另外两种赋值方式
1、通过执行 SQL 任务组件
添加执行 SQL 任务,编辑任务,在常规页面->SQL语句 可以从相关的表中取数据。这里为了方便,直接select 常量(为了体现不一样,取06年到07年的数据),ResultSet选择单行。结果集页面添加查询语句返回列与变量的对应关系。

运行包,得到了06-07年的数据,当然可以参照前面调整OLEDB源中的代码。

--22:11 2014/5/19 补充
'执行SQL任务'也可以是一个返回多条记录的结果集,利用'Foreach 循环容器'循环这个结果集的每一条记录,根据这条记录的某几个字段执行'数据流任务'。
首先添加一个Object类型的变量ResultList用来存储返回的主结果集:

'执行SQL任务'->常规->SQL语句 从相关的表中取数据,ResultSet选择完整结果集:

参数映射,将主结果集中返回的字段按需要map到相应的定义好的变量上,记得Direction设为Input(有人说要设成Output,但我用Output测试就出错)。ParameterName一定要是数字,并以0开始与主结果集中的字段相对应。下图是我的主结果集及映射关系:

结果集页,增加一个Result,ResultName一定要设置成0,VariableName为我们之前定义好的ResultList:

增加一个'Foreach 循环容器'控件,编辑容器属性
切换到集合页,将Enumerator设置为Foreach ADO 枚举器以循环结果集。ADO 对象源变量选择ResultList。选择Rows in the first table,如下图:

变量映射页,配置变量的对应关系,其中Index跟前面的配置要对应:

在容器内添加数据流任务,便可循环执行操作:

2、通过脚本组件
暂时没用过,略...

posted @ 2014-05-18 18:23  Uest  阅读(4133)  评论(2编辑  收藏  举报