ssis - 如何在SSIS控制流任务中创建临时表，然后在VAL流任务中使用它？

Question

我有一个控制流程，可在其中使用T-SQL命令在其中创建临时数据库和表。当我添加数据流时，我想查询表，但是不能，因为表不存在，无法从中获取信息。当我尝试时，由于数据库不存在，登录时出现错误(尚未)。我将延迟验证设为true。

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

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

Answer 1

解:
将“连接管理器”上的属性RetainSameConnection设置为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
Create a table named dbo.StateProvince that will be used as the destination table to populate the records from temporary table. Use the below create table script to create the destination table.
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包。右键单击程序包底部的“连接管理器”选项卡，然后单击“新建OLE DB连接...”以创建新的连接以访问SQL Server 2008 R2数据库。

在“配置OLE DB连接管理器”上单击“新建...”。

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

从提供程序中选择本机OLE DB \ SQL Server本机客户端10.0
因为该软件包将连接到SQL Server 2008 R2数据库

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

从“登录到服务器”部分中选择“使用Windows身份验证”，或者选择您喜欢的任何一个。

从“选择”中选择数据库或输入数据库名称，然后
示例使用数据库名称Sora。

单击测试连接

在“测试连接成功”消息上单击“确定”。

在连接管理器上单击“确定”。

新创建的数据连接将出现在“配置OLE DB连接管理器”上。单击确定。

OLE DB连接管理器KIWI \ SQLSERVER2008R2.Sora将显示在包底部的“连接管理器”选项卡下。右键单击连接管理器，然后单击属性

将连接KIWI \ SQLSERVER2008R2.Sora上的属性RetainSameConnection设置为True。

右键单击包内的任何位置，然后单击“变量”以查看“变量”窗格。创建以下变量。
包范围SO_5631010中数据类型为String的名为PopulateTempTable的新变量，并将变量的值设置为EXEC dbo.PopulateTempTable。
包范围SO_5631010中名为String的数据类型的名为FetchTempData的新变量，并将该变量设置为值SELECT CountryCode，StateCode，Name FROM ## tmpStateProvince

将“执行SQL任务”拖放到“控制流”选项卡上。双击执行SQL任务以查看执行SQL任务编辑器。
在“执行SQL任务编辑器”的“常规”页面上，执行以下操作。
将“名称”设置为“创建并填充临时表”
将连接类型设置为OLE DB
将连接设置为KIWI \ SQLSERVER2008R2.Sora
从SQLSourceType选择变量
从SourceVariable中选择User::PopulateTempTable
点击确定

将数据流任务拖放到“控制流”选项卡上。将数据流任务重命名为将临时数据传输到数据库表。将绿色箭头从“执行SQL任务”连接到“数据流任务”。

双击“数据流任务”以切换到“数据流”选项卡。将OLE DB源拖放到“数据流”选项卡上。双击OLE DB源以查看OLE DB源编辑器。
在“OLE DB源编辑器”的“连接管理器”页面上，执行以下操作。
从OLE DB连接管理器中选择KIWI \ SQLSERVER2008R2.Sora
从数据访问模式的变量中选择SQL命令
从变量名称中选择User::FetchTempData
点击列页面

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

要解决此错误，请在数据库Sora上使用SQL Server Management Studio(SSMS)执行语句EXEC dbo.PopulateTempTable，以便存储过程将创建临时表。执行存储过程后，单击OLE DB Source Editor上的Columns页面，您将看到列信息。单击确定。

将OLE DB目标拖放到“数据流”选项卡上。将绿色箭头从OLE DB源连接到OLE DB目标。双击OLE DB目标以打开OLE DB目标编辑器。
在“OLE DB目标编辑器”的“连接管理器”页面上，执行以下操作。
从OLE DB连接管理器中选择KIWI \ SQLSERVER2008R2.Sora
选择表或 View -从数据访问模式快速加载
从表或 View 的名称中选择[dbo]。[StateProvince]
单击映射页面

如果输入和输出列名称相同，则OLE DB目标编辑器上的“单击映射”页面将自动映射列。单击确定。列StateProvinceID没有匹配的输入列，并且在数据库中被定义为IDENTITY列。因此，不需要映射。

配置所有组件后，“数据流”选项卡应该看起来像这样。

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

执行查询，从SQL Server Management Studio(SSMS)的dbo.StateProvince中选择*，以查找表中的行数。在执行软件包之前，它应该为空。

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

在“数据流”选项卡中，您会注意到该程序包成功处理了6行。在此发布的早期创建的存储过程在临时表中插入了6行。

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

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