解决方案:
设置属性RetainSameConnection
on the Connection Manager
to True
以便在一个控制流任务中创建的临时表可以保留在另一任务中。
这是一个用以下语言编写的示例 SSIS 包SSIS 2008 R2
这说明了如何使用临时表。
演练:
创建一个存储过程,该过程将创建一个名为的临时表##tmpStateProvince
并填充很少的记录。示例 SSIS 包将首先调用存储过程,然后获取临时表数据以将记录填充到另一个数据库表中。示例包将使用名为的数据库Sora
使用下面的创建存储过程脚本。
USE Sora;
GO
CREATE PROCEDURE dbo.PopulateTempTable
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('TempDB..##tmpStateProvince') IS NOT NULL
DROP TABLE ##tmpStateProvince;
CREATE TABLE ##tmpStateProvince
(
CountryCode nvarchar(3) NOT NULL
, StateCode nvarchar(3) NOT NULL
, Name nvarchar(30) NOT NULL
);
INSERT INTO ##tmpStateProvince
(CountryCode, StateCode, Name)
VALUES
('CA', 'AB', 'Alberta'),
('US', 'CA', 'California'),
('DE', 'HH', 'Hamburg'),
('FR', '86', 'Vienne'),
('AU', 'SA', 'South Australia'),
('VI', 'VI', 'Virgin Islands');
END
GO
创建一个名为dbo.StateProvince
它将用作目标表来填充临时表中的记录。使用下面的创建表脚本创建目标表。
USE Sora;
GO
CREATE TABLE dbo.StateProvince
(
StateProvinceID int IDENTITY(1,1) NOT NULL
, CountryCode nvarchar(3) NOT NULL
, StateCode nvarchar(3) NOT NULL
, Name nvarchar(30) NOT NULL
CONSTRAINT [PK_StateProvinceID] PRIMARY KEY CLUSTERED
([StateProvinceID] ASC)
) ON [PRIMARY];
GO
使用创建 SSIS 包Business Intelligence Development Studio (BIDS)
。右键单击连接管理器包底部的选项卡,然后单击New OLE DB Connection...
创建一个新的连接来访问SQL Server 2008 R2数据库。
Click New...
on 配置 OLE DB 连接管理器.
在上执行以下操作连接管理器 dialog.
- Select
Native OLE DB\SQL Server Native Client 10.0
from Provider因为包将连接到SQL Server 2008 R2 数据库
- 输入服务器名称, like
MACHINENAME\INSTANCE
- Select
Use Windows Authentication
from 登录服务器部分或您喜欢的部分。
- 从以下位置选择数据库
Select or enter a database name
,示例使用数据库名称Sora
.
- Click
Test Connection
- Click
OK
on the 测试连接成功信息。
- Click
OK
on 连接管理器
新创建的数据连接将出现在配置 OLE DB 连接管理器. Click OK
.
OLE DB 连接管理器KIWI\SQLSERVER2008R2.Sora
将出现在连接管理器标签位于包装底部。右键单击连接管理器,然后单击Properties
设置属性RetainSameConnection
在连接上KIWI\SQLSERVER2008R2.Sora
到值True
.
右键单击包内的任意位置,然后单击Variables
查看变量窗格。创建以下变量。
-
一个新变量名为PopulateTempTable
数据类型的String
在包范围内SO_5631010
并设置变量的值EXEC dbo.PopulateTempTable
.
-
一个新变量名为FetchTempData
数据类型的String
在包范围内SO_5631010
并设置变量的值SELECT CountryCode, StateCode, Name FROM ##tmpStateProvince
拖放Execute SQL Task
到控制流标签。双击执行SQL任务可以查看执行 SQL 任务编辑器.
On the General
的页面执行 SQL 任务编辑器,执行以下操作。
- Set the Name to
Create and populate temp table
- Set the 连接类型 to
OLE DB
- Set the 联系 to
KIWI\SQLSERVER2008R2.Sora
- Select
Variable
from SQL源类型
- Select
User::PopulateTempTable
from 源变量
- Click
OK
拖放Data Flow Task
到控制流标签。将数据流任务重命名为Transfer temp data to database table
。连接绿色箭头执行SQL任务 to the 数据流任务.
双击Data Flow Task
切换到数据流标签。拖放OLE DB Source
到数据流标签。双击OLE DB 源查看OLE DB 源编辑器.
On the Connection Manager
的页面OLE DB 源编辑器,执行以下操作。
- Select
KIWI\SQLSERVER2008R2.Sora
from OLE DB 连接管理器
- Select
SQL command from variable
from 数据存取方式
- Select
User::FetchTempData
from 变量名
- Click
Columns
page
点击Columns
页上OLE DB 源编辑器将显示以下错误,因为表##tmpStateProvince
源命令中指定的变量不存在,SSIS 无法读取列定义。
要修复错误,请执行该语句EXEC dbo.PopulateTempTable
using SQL Server 管理工作室 (SSMS)在数据库上Sora
这样存储过程就会创建临时表。执行存储过程后,单击Columns
页上OLE DB 源编辑器,您将看到列信息。点击OK
.
拖放OLE DB Destination
到数据流标签。连接绿色箭头OLE DB 源 to OLE DB 目标。双击OLE DB Destination
打开OLE DB 目标编辑器.
On the Connection Manager
的页面OLE DB 目标编辑器,执行以下操作。
- Select
KIWI\SQLSERVER2008R2.Sora
from OLE DB 连接管理器
- Select
Table or view - fast load
from 数据存取方式
- Select
[dbo].[StateProvince]
from Name表或视图的
- Click
Mappings
page
Click Mappings
页面上的OLE DB 目标编辑器如果输入和输出列名称相同,将自动映射列。点击OK
。柱子StateProvinceID
没有匹配的输入列,它被定义为IDENTITY
数据库中的列。因此,不需要映射。
数据流配置完所有组件后,选项卡应如下所示。
点击OLE DB Source
on 数据流选项卡并按下F4查看Properties
。设置属性ValidateExternalMetadata
设置为 False,以便 SSIS 在包执行的验证阶段不会尝试检查临时表是否存在。
执行查询select * from dbo.StateProvince
in the SQL Server 管理工作室 (SSMS)查找表中的行数。在执行包之前它应该是空的。
执行包。控制流显示执行成功。
在“数据流”选项卡中,您会注意到包已成功处理6行。插入此帖子早期创建的存储过程6行存入临时表。
执行查询select * from dbo.StateProvince
in the SQL Server 管理工作室 (SSMS)找到6行已成功插入表中。数据应与存储过程中找到的行匹配。
上面的示例说明了如何在包中创建和使用临时表。