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

Question

我有一个控制流，我使用 T-SQL 命令创建临时数据库和表。当我添加数据流时，我想查询该表，但我不能，因为该表不存在可从中获取信息。当我尝试时，我收到有关登录的错误，因为数据库尚不存在。我已将连接管理器的属性 DelayValidation 设置为 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

创建一个名为 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 包。Business Intelligence Development Studio (BIDS)。右键单击包底部的“连接管理器”选项卡，然后单击“新建 OLE DB 连接...”以创建新连接访问 SQL Server 2008 R2 数据库。

单击新建... 配置 OLE DB 连接管理器。

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

• 从Provider中选择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。

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

• 包范围 中名为 PopulateTempTable 的新变量，数据类型为 String SO_5631010 并将变量设置为值 EXEC dbo.PopulateTempTable。

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

将执行 SQL 任务拖放到控制流选项卡上。双击“执行 SQL 任务”以查看“执行 SQL 任务编辑器”。

在执行 SQL 任务编辑器的常规页面上，执行以下操作。

• 将名称设置为创建并填充临时表
• 将连接类型设置为OLE DB
• 将连接设置为KIWI\SQLSERVER2008R2.Sora
• 选择变量 from SQLSourceType 从
• SourceVariable中选择User::PopulateTempTable
• 单击确定< /code>

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

双击数据流任务进行切换到数据流选项卡。将 OLE DB 源 拖放到数据流 选项卡上。双击OLE DB 源以查看OLE DB 源编辑器。

在OLE DB 源编辑器 的连接管理器 页面上，执行以下操作。

• 从OLE DB 连接管理器中选择KIWI\SQLSERVER2008R2.Sora
• 从变量中选择SQL 命令 > 从数据访问模式
• 从变量名称中选择User::FetchTempData
• 点击Columns< /code>页

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

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

拖放 OLE DB 目标到数据流选项卡。将绿色箭头从OLE DB 源连接到OLE DB 目标。双击OLE DB 目标 打开OLE DB 目标编辑器。

在OLE DB 目标编辑器 的连接管理器 页面上，执行以下操作。

• 从OLE DB 连接管理器中选择KIWI\SQLSERVER2008R2.Sora
• 选择表或视图 - 快速加载< /em> 来自数据访问模式
• 从表或表的名称中选择[dbo].[StateProvince]查看
• 单击映射页面

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

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

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

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

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

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

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

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

Answer 2

我参加这个聚会迟到了，但我想为 user756519 的彻底、出色的回答添加一点内容。根据我最近的经验，我不认为“连接管理器上的 RetainSameConnection”属性与此实例相关。就我而言，相关的一点是他们建议将“ValidateExternalMetadata”设置为 False。

我使用临时表来促进将数据从一个数据库（和服务器）复制到另一个数据库，因此“RetainSameConnection”的原因与我的特定情况无关。我也不认为完成本例中发生的事情很重要，尽管它很彻底。

Answer 3

关于

1. 此答案提供了 Microsoft Visual Studio 2017 中 SSIS 的屏幕截图。

2. 虽然此处的另一个答案要求您运行存储过程，但此处的此答案连接到正确的“tempdb”数据库，并且不需要存储过程。对于那些想要避免使用存储过程向生产服务器发送垃圾邮件的人来说，这可能会很好。

这个答案表明您可以在控制流内使用临时表并将它们作为 DFT 中的目标，您也可以在 在 SSIS 中使用临时表？，正如我后来发现的，但该指南忘记显示它的数据流任务方面。如果在控制流窗格中创建临时表后更改为 DFT 窗格，则可以与“tempdb”数据库建立新连接，并找到在控制流中创建的仍然有效的临时表。

tempdb 系统数据库作为临时表的唯一数据库

如果我逐字逐句地理解这个问题的答案，那就是：没有答案。您需要一个保存在新数据库中的临时表，该表在运行时生效。但临时表始终保存在服务器级别的系统数据库“tempdb”中，请参阅tempdb 数据库 - Microsoft - Learn - SQL - SQL Server。您不能将临时表放入您自己的数据库中。奇怪的是，我在 Stack Overflow 上发现只有一条评论强调了这一点，请参阅 检查临时表是否存在，如果存在则删除创建临时表。

因此，问题还剩一半，你问可以做什么。答：不要为临时表创建新的数据库。相反，按原样使用服务器并在“tempdb”系统数据库中创建临时表。没有其他办法。如果您随后在 SSIS 中采用此连接，您还可以像处理普通表一样处理临时表。您不需要存储过程来进行设置。您可以从“tempdb”数据库中选择临时表并将其作为SSIS中的“OLE DB”对象。

屏幕截图中的步骤

建立 tempdb 连接

< img src="https://i.sstatic.net/4goiY.png" alt="在此处输入图像描述">

主要技巧：将“RetainSameConnection”更改为 True

正如其他答案正确所说，这是唯一的方法在控制流步骤之间保持临时表处于活动状态。

控制流

制作并填充临时表

其他序列容器需要“延迟验证”= True

如果您需要其他序列中的临时表，则 在创建临时表之后的容器，您还需要在序列容器的属性中将Delay Validation设置为True，否则您获取 SSIS 验证失败并返回验证状态“VS_ISBROKEN”。

，如果您在其他容器中看到这些红叉，则此设置可以避免在包尚未运行时检查那些由于缺少临时表而产生的错误。一旦你运行它并使临时表处于活动状态，一旦 SSIS 到达红色标记，它们就会消失：

控制流 3：写入 TempDB ##tmpTest

从最后一个控制流步骤 3（数据）开始Flow Task作为写入TempDB临时表的序列容器的核心。从这里开始，您将了解控制流步骤 2 中表所需的数据类型。

OLE DB 源编辑器

SELECT 'Hello' AS Test

OLE DB 目标对象

< a href="https://i.sstatic.net/sKC5d.png" rel="nofollow noreferrer">

CREATE TABLE ##tmpTest(
 [Test] [varchar](5) NULL
)

复制单击“新建”时获得的代码。

OLE DB 目标编辑器

控制流程 2：

使用复制的代码制作 TempDB ##tmpTest 创建表：

OLE DB 目标编辑器

控制流程1: Drop TempDB ##tmpTest

这不是必需的，因为一旦控制流停止，临时表就会丢失，请参阅如何查看临时表sql server 中的代码创建的表？。但是，如果您担心某人（或您）可能在 SSMS 中的同一个临时表上工作，以便在容器启动时它可能不为空，那么您是安全的。

IF OBJECT_ID(N'tempdb.dbo.##tmpTest') IS NOT NULL
        DROP TABLE dbo.##tmpTest;

或者在 2016 年服务器上，您可以运行 DROP TABLE IF EXISTS ##tmpTest;

请参阅 在创建临时表之前检查临时表是否存在并删除它是否存在，并注意同一链接：

您可以截断并重用它，而不是删除并重新创建临时表。

另请参阅 如何查看代码创建的临时表在 SQL Server 中？。

运行并检查

如果无法访问该表，您将看到：

如果有效，您会看到：

在调试模式下保持绿色，您将看到临时表仍然活着：

现在退出调试模式：

如果刷新，临时表将消失：

表格为填充：

您可以继续使用此表，就好像它不是临时的一样，并用它提供另一个查询或表。由于它前面有两个主题标签“##”，因此它是全局的，您可以在整个包中使用它，直到包停止运行。一旦包停止并且您未处于调试模式，临时表将被删除。