ssis - 如何在SSIS控制流任务中创建一个临时表然后在数据流任务中使用它？

Question

我有一个控制流，我在其中使用 T-SQL 命令创建临时数据库和表。当我添加数据流时，我想查询该表，但我不能，因为该表不存在可从中获取信息。当我尝试时，我收到有关登录的错误，因为数据库不存在(还)。我有延迟验证为真。

如果我手动创建数据库和表，然后添加带有查询的数据流并删除它坚持的数据库，但它似乎不是一个干净的解决方案。

如果有更好的方法来创建临时登台数据库并在数据流中查询它，请告诉我。

Answer 1

解决方案:
设置属性 RetainSameConnection 在 Connection Manager至 True 以便在一个控制流任务中创建的临时表可以保留在另一个任务中。
这是用 SSIS 2008 R2 编写的示例 SSIS 包这说明了使用临时表。
演练:
创建一个存储过程，它将创建一个名为 ##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

使用 Business Intelligence Development Studio (BIDS) 创建 SSIS 包.右键单击包底部的连接管理器选项卡，然后单击 New OLE DB Connection...创建新连接以访问 SQL Server 2008 R2 数据库。

点击 New...在配置 OLE DB 连接管理器上。

在连接管理器对话框上执行以下操作。

选择 Native OLE DB\SQL Server Native Client 10.0来自提供程序，因为包将连接到 SQL Server 2008 R2 数据库

输入服务器名称，如 MACHINENAME\INSTANCE

选择 Use Windows Authentication从登录到服务器部分或您喜欢的任何部分。

从 Select or enter a database name 中选择数据库，示例使用数据库名称 Sora .

点击Test Connection

点击OK测试连接成功消息。

点击OK连接管理器

新创建的数据连接将出现在配置 OLE DB 连接管理器上。点击 OK .

OLE DB 连接管理器 KIWI\SQLSERVER2008R2.Sora 将出现在包底部的连接管理器选项卡下。右键单击连接管理器，然后单击 Properties
设置属性 RetainSameConnection关于连接 KIWI\SQLSERVER2008R2.Sora到值 True .

右键单击包内的任意位置，然后单击 Variables查看变量 Pane 。创建以下变量。

一个名为 PopulateTempTable 的新变量数据类型 String在包范围内 SO_5631010并将变量设置为值 EXEC dbo.PopulateTempTable .

一个名为 FetchTempData 的新变量数据类型 String在包范围内 SO_5631010并将变量设置为值 SELECT CountryCode, StateCode, Name FROM ##tmpStateProvince

拖放 Execute SQL Task到控制流选项卡。双击执行 SQL 任务以查看执行 SQL 任务编辑器。
关于 General执行 SQL 任务编辑器的页面，执行以下操作。

将名称设置为 Create and populate temp table

将连接类型设置为 OLE DB

将连接设置为 KIWI\SQLSERVER2008R2.Sora

选择 Variable来自 SQLSourceType

选择 User::PopulateTempTable来自 SourceVariable

点击OK

拖放 Data Flow Task到控制流选项卡。将数据流任务重命名为 Transfer temp data to database table .将绿色箭头从执行 SQL 任务连接到数据流任务。

双击 Data Flow Task切换到数据流选项卡。拖放 OLE DB Source到数据流选项卡。双击 OLE DB Source 以查看 OLE DB Source Editor。
关于 Connection Manager OLE DB 源编辑器的页面，执行以下操作。

选择 KIWI\SQLSERVER2008R2.Sora来自 OLE DB 连接管理器

选择 SQL command from variable来自数据访问方式

选择 User::FetchTempData来自变量名

点击Columns页

点击 Columns OLE DB 源代码编辑器上的页面将显示以下错误，因为表 ##tmpStateProvince 源命令变量中指定的变量不存在，SSIS 无法读取列定义。

要修复错误，请执行语句 EXEC dbo.PopulateTempTable 在数据库上使用 SQL Server Management Studio (SSMS) Sora以便存储过程将创建临时表。执行存储过程后，点击 Columns OLE DB 源代码编辑器上的页面，您将看到列信息。点击 OK .

拖放 OLE DB Destination到数据流选项卡。将绿色箭头从 OLE DB 源连接到 OLE DB 目标。双击 OLE DB Destination打开 OLE DB 目标编辑器。
关于 Connection Manager OLE DB 目标编辑器的页面，执行以下操作。

选择 KIWI\SQLSERVER2008R2.Sora来自 OLE DB 连接管理器

选择 Table or view - fast load来自数据访问方式

选择 [dbo].[StateProvince]来自表或 View 的名称

点击Mappings页

点击 Mappings如果输入和输出列名称相同，OLE DB 目标编辑器上的页面将自动映射列。点击 OK .栏目 StateProvinceID没有匹配的输入列，它被定义为 IDENTITY数据库中的列。因此，不需要映射。

配置完所有组件后，“数据流”选项卡应如下所示。

点击 OLE DB Source在“数据流”选项卡上，然后按 F4 查看 Properties .设置属性 ValidateExternalMetadata为 False，以便 SSIS 在包执行的验证阶段不会尝试检查临时表的存在。

执行查询 select * from dbo.StateProvince在 SQL Server Management Studio (SSMS) 中查找表中的行数。在执行包之前它应该是空的。

执行包。控制流显示成功执行。

在数据流选项卡中，您会注意到包已成功处理 6 行。在这篇文章中早期创建的存储过程插入 6 行到临时表中。

执行查询 select * from dbo.StateProvince在 SQL Server Management Studio (SSMS) 中找到 6 行成功插入到表中。数据应与在存储过程中找到的行匹配。

上面的例子说明了如何在包中创建和使用临时表。