如何在SSIS控制流任务中创建临时表并在VAL流任务中使用它？

Question

我有一个控制流程，其中我使用T-SQL命令创建了一个临时数据库和表。当我添加数据流时，我想查询该表，但是我不能这样做，因为该表不存在，无法获取信息。尝试时，会因为数据库不存在而报错需要登录。我将验证延迟设置为true。

如果我手动创建数据库和表，然后使用查询添加数据流，并删除数据库，则可以实现目标，但这似乎不是一个干净的解决方案。

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

- Farhad Bagherlo

---

你可以尝试使用脚本任务来生成源代码... - e4rthdog

你是在创建临时表还是临时数据库？ - billinkc

你可以使用一个对象类型的变量来代替临时表吗？ - Tab Alleman

Answer 1

如果你非常需要一个只在程序执行的短暂时刻存在的数据库，那么请按照以下步骤进行操作... 首先，创建一个执行SQL语句的控制流项来创建数据库。接着是另一个执行SQL语句的控制流项来创建表格。然后，将数据流导入到表格中并进行查询。最后，在最后一个控制流项中，使用DROP数据库命令的执行SQL语句。完成，临时数据库。

这种方法的注意点 - 正如你所注意到的 - 是为了设计数据流，必须首先存在数据库。这是无法避免的。我们设计数据流的对象必须存在才能进行设计。然而，这个问题只存在于设计阶段。它是暂时的。一旦数据流程设计完成，您可以保存包并删除数据库。它会一直保持删除状态，直到您再次运行该包。设置“延迟验证”可确保您的数据流不会尝试在包的顶部验证数据库的存在性。它会在CREATE数据库语句之后，恰好在需要时等待。因此，您可以放心使用。

然而，在您实施这种设计之前，我必须问一下。为什么我们要像使用变量一样使用数据库？为什么仅创建它，然后立即删除它？我猜测这是因为您希望将数据存储在SQL中，以便可以利用SQL语言进行有选择性的查询和/或操作，如果是这样，那么SSIS的目的就是使用提供的数据流转换工具执行此类操作或查询。此外，您在问题中没有提到，但数据的最终目的地是什么？我们不只是将其加载到数据库中并删除它。它最终会到哪里？一个平面文件吗？

- Troy Witthoeft

Answer 2

解决方案： 将连接管理器上的RetainSameConnection属性设置为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
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

使用商业智能开发工具（BIDS）创建一个SSIS包。在包底部的连接管理器选项卡上右键单击，然后单击“新建OLE DB连接...”以创建一个新的连接来访问SQL Server 2008 R2数据库。

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

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

- 从提供程序中选择Native OLE DB\SQL Server Native Client 10.0，因为该包将连接到SQL Server 2008 R2数据库。 - 输入服务器名称，例如MACHINENAME\INSTANCE。 - 从“登录到服务器”部分中选择“使用Windows身份验证”，或者您喜欢的其他方式。 - 从“选择或输入数据库名称”中选择数据库，示例使用的数据库名称为Sora。 - 单击“测试连接”。 - 在“测试连接成功”消息上单击“确定”。 - 在“连接管理器”上单击“确定”。

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

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

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

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

将Execute SQL Task拖放到控制流选项卡上。双击Execute SQL Task以查看Execute SQL Task Editor。 在Execute SQL Task Editor的General页面上，执行以下操作。 将名称设置为Create and populate temp table 将连接类型设置为OLE DB 将连接设置为KIWI\SQLSERVER2008R2.Sora 从SQLSourceType中选择Variable 从SourceVariable中选择User::PopulateTempTable 单击“确定”

将Data Flow Task拖放到控制流选项卡上。将Data Flow Task重命名为Transfer temp data to database table。将绿色箭头从Execute SQL Task连接到Data Flow Task。

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

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

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

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

单击OLE DB目标编辑器的映射页会自动映射列，如果输入和输出列名称相同。单击确定。数据库中没有匹配输入列的状态省份ID列被定义为标识列。因此，不需要进行映射。

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

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

在SQL Server Management Studio（SSMS）中执行查询select * from dbo.StateProvince，以查找表中的行数。在执行包之前，它应该为空。

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

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

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

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

- Elay