如何在SSIS控制流任务中创建一个临时表然后在数据流任务中使用它? [英] How to create a temporary table in SSIS control flow task and then use it in data flow task?

查看：34
本文介绍了如何在SSIS控制流任务中创建一个临时表然后在数据流任务中使用它?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I have a control flow where I create a temp database and table in a with a T-SQL Command. When I add a dataflow I would like to query the table but I can't because the table doesn't exist to grab information from. When I try I get errors about logging in because the database doesn't exist (yet). I have delay validation to true.

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

If I create the database and table manually then add the dataflow with query and drop the database it sticks but it doesn't seem like a clean solution.

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

If there is a better way to create a temporary staging database and query it in dataflows please let me know.

推荐答案

解决方案:

将Connection Manager 上的RetainSameConnection 属性设置为True 以便在一个控制流任务中创建的临时表可以保留在另一个任务中.

Solution:

Set the property RetainSameConnection on the Connection Manager to True so that temporary table created in one Control Flow task can be retained in another task.

这是一个用 SSIS 2008 R2 编写的示例 SSIS 包，它说明了使用临时表.

Here is a sample SSIS package written in SSIS 2008 R2 that illustrates using temporary tables.

创建一个存储过程，该过程将创建一个名为 ##tmpStateProvince 的临时表并填充少量记录.示例 SSIS 包将首先调用存储过程，然后获取临时表数据以将记录填充到另一个数据库表中.示例包将使用名为 Sora 的数据库使用以下创建存储过程脚本.

Create a stored procedure that will create a temporary table named ##tmpStateProvince and populate with few records. The sample SSIS package will first call the stored procedure and then will fetch the temporary table data to populate the records into another database table. The sample package will use the database named Sora Use the below create stored procedure script.

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 的表，该表将用作从临时表中填充记录的目标表.使用下面的创建表脚本来创建目标表.

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 包.右键单击包底部的 Connection Managers 选项卡，然后单击 New OLE DB Connection... 以创建一个新的连接到访问SQL Server 2008 R2 数据库.

Create an SSIS package using Business Intelligence Development Studio (BIDS). Right-click on the Connection Managers tab at the bottom of the package and click New OLE DB Connection... to create a new connection to access SQL Server 2008 R2 database.

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

Click New... on Configure OLE DB Connection Manager.

在 Connection Manager 对话框中执行以下操作.

Perform the following actions on the Connection Manager dialog.

  • 从 Provider 中选择 Native OLE DB\SQL Server Native Client 10.0 因为该包将连接到 SQL Server 2008 R2 数据库
  • 输入服务器名称，如MACHINENAME\INSTANCE
  • 从登录服务器部分或您喜欢的任何选项中选择使用 Windows 身份验证.
  • 从选择或输入数据库名称中选择数据库，示例使用数据库名称Sora.
  • 点击测试连接
  • 在测试连接成功消息上点击OK.
  • 在Connection Manager
    • 上点击OK
  • Select Native OLE DB\SQL Server Native Client 10.0 from Provider since the package will connect to SQL Server 2008 R2 database
  • Enter the Server name, like MACHINENAME\INSTANCE
  • Select Use Windows Authentication from Log on to the server section or whichever you prefer.
  • Select the database from Select or enter a database name, the sample uses the database name Sora.
  • Click Test Connection
  • Click OK on the Test connection succeeded message.
  • Click OK on Connection Manager

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

The newly created data connection will appear on Configure OLE DB Connection Manager. Click OK.

OLE DB 连接管理器 KIWI\SQLSERVER2008R2.Sora 将出现在包底部的 Connection Manager 选项卡下.右键单击连接管理器，然后单击 Properties

OLE DB connection manager KIWI\SQLSERVER2008R2.Sora will appear under the Connection Manager tab at the bottom of the package. Right-click the connection manager and click Properties

将连接 KIWI\SQLSERVER2008R2.Sora 上的属性 RetainSameConnection 设置为值 <代码>真.

Set the property RetainSameConnection on the connection KIWI\SQLSERVER2008R2.Sora to the value True.

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

Right-click anywhere inside the package and then click Variables to view the variables pane. Create the following variables.

  • 一个名为 PopulateTempTable 的新变量 String 在包作用域中的数据类型 SO_5631010 并将变量设置为 EXEC dbo.PopulateTempTable.

一个名为 FetchTempData 的新变量，数据类型为 String 在包作用域中 SO_5631010 并将变量设置为 SELECT CountryCode, StateCode, Name FROM ##tmpStateProvince

A new variable named FetchTempData of data type String in the package scope SO_5631010 and set the variable with the value SELECT CountryCode, StateCode, Name FROM ##tmpStateProvince

将 Execute SQL Task 拖放到 Control Flow 选项卡上.双击执行 SQL 任务以查看执行 SQL 任务编辑器.

Drag and drop an Execute SQL Task on to the Control Flow tab. Double-click the Execute SQL Task to view the Execute SQL Task Editor.

在执行 SQL 任务编辑器的General页面上，执行以下操作.

On the General page of the Execute SQL Task Editor, perform the following actions.

  • 将名称设置为创建并填充临时表
  • 将连接类型设置为OLE DB
  • 将连接设置为KIWI\SQLSERVER2008R2.Sora
  • 从SQLSourceType
    • 中选择Variable
  • 从SourceVariable
    • 中选择User::PopulateTempTable
  • 点击OK

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

Drag and drop a Data Flow Task onto the Control Flow tab. Rename the Data Flow Task as Transfer temp data to database table. Connect the green arrow from the Execute SQL Task to the Data Flow Task.

双击Data Flow Task 切换到Data Flow 标签.将 OLE DB Source 拖放到 Data Flow 选项卡上.双击 OLE DB Source 以查看 OLE DB Source Editor.

Double-click the Data Flow Task to switch to Data Flow tab. Drag and drop an OLE DB Source onto the Data Flow tab. Double-click OLE DB Source to view the OLE DB Source Editor.

在 OLE DB Source Editor 的 Connection Manager 页面上，执行以下操作.

On the Connection Manager page of the OLE DB Source Editor, perform the following actions.

  • 从OLE DB 连接管理器
    • 选择KIWI\SQLSERVER2008R2.Sora
  • 从数据访问模式
    • 中选择SQL command from variable
  • 从变量名
    • 中选择User::FetchTempData
  • 点击Columns页面

在OLE DB Source Editor上点击Columns页面会显示如下错误，因为表##tmpStateProvince<源命令变量中指定的/code> 不存在且 SSIS 无法读取列定义.

Clicking Columns page on OLE DB Source Editor will display the following error because the table ##tmpStateProvince specified in the source command variable does not exist and SSIS is unable to read the column definition.

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

To fix the error, execute the statement EXEC dbo.PopulateTempTable using SQL Server Management Studio (SSMS) on the database Sora so that the stored procedure will create the temporary table. After executing the stored procedure, click Columns page on OLE DB Source Editor, you will see the column information. Click OK.

将 OLE DB Destination 拖放到 Data Flow 选项卡上.将绿色箭头从 OLE DB Source 连接到 OLE DB Destination.双击OLE DB Destination打开OLE DB Destination Editor.

Drag and drop OLE DB Destination onto the Data Flow tab. Connect the green arrow from OLE DB Source to OLE DB Destination. Double-click OLE DB Destination to open OLE DB Destination Editor.

在 OLE DB Destination Editor 的 Connection Manager 页面上，执行以下操作.

On the Connection Manager page of the OLE DB Destination Editor, perform the following actions.

  • 从OLE DB 连接管理器
    • 选择KIWI\SQLSERVER2008R2.Sora
  • 从数据访问模式
    • 中选择表格或视图 - 快速加载
  • 从表或视图的名称中选择[dbo].[StateProvince]
  • 点击Mappings页面

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

Click Mappings page on the OLE DB Destination Editor would automatically map the columns if the input and output column names are same. Click OK. Column StateProvinceID does not have a matching input column and it is defined as an IDENTITY column in database. Hence, no mapping is required.

Data Flow 选项卡在配置完所有组件后应如下所示.

Data Flow tab should look something like this after configuring all the components.

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

Click the OLE DB Source on Data Flow tab and press F4 to view Properties. Set the property ValidateExternalMetadata to False so that SSIS would not try to check for the existence of the temporary table during validation phase of the package execution.

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

Execute the query select * from dbo.StateProvince in the SQL Server Management Studio (SSMS) to find the number of rows in the table. It should be empty before executing the package.

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

Execute the package. Control Flow shows successful execution.

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

In Data Flow tab, you will notice that the package successfully processed 6 rows. The stored procedure created early in this posted inserted 6 rows into the temporary table.

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

Execute the query select * from dbo.StateProvince in the SQL Server Management Studio (SSMS) to find the 6 rows successfully inserted into the table. The data should match with rows founds in the stored procedure.

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

The above example illustrated how to create and use temporary table within a package.

这篇关于如何在SSIS控制流任务中创建一个临时表然后在数据流任务中使用它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆