主页 / dba / 问题 / 338395
Accepted
questionto42
Asked: 2024-04-08 01:30:57 +0800 CST

DFT 在留下任何 DFT 项目后会删除连接及其临时表，使用两个脚本组件进行测试。如何使临时表保持活动状态？

  • 772

链接

这占用：

由此，我认为我应该测试保持两个 DFT 项之间的连接，以便临时表不会被删除。

控制流连接管理器无法修复它

使用“tempdb”数据库的连接管理器，我可以在控制流中创建临时表，这些表在控制流内的下一步中仍然存在，请参阅在 SSIS 中使用临时表？。你可能会想，如何在SSIS控制流任务中创建临时表，然后在数据流任务中使用它？已经回答了这个问题，我发现那里的答案并没有解决 DFT 中一步后删除临时表的问题。

ADO.NET 连接管理器无法修复此问题

我使用 ADO.NET 连接管理器测试了 DFT，临时表在脚本组件之后仍然被删除。

在此输入图像描述

测试设置

我在一个依次放置的脚本组件上测试了它：

在此输入图像描述

设置RetainSameConnection为 并True不能修复它

我设置RetainSameConnection为True其他链接中的那样，但这并没有解决问题。

代码技巧

我尝试通过不在第二个脚本组件中建立新连接而只是获取它来进行尝试，但无济于事：

    public override void PostExecute()
    {
        //base.PostExecute();
        // here we would bulk copy data into the temp table
        // you may need to batch this operation or handle it differently based on your situation
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
        {
            // Now write that DataTable to the database 
            bulkCopy.DestinationTableName = "##tmpTable";
            bulkCopy.WriteToServer(dt);
            bulkCopy.Close();
        }
        //conn.Close();
    }
  • 在脚本组件 2 中，只需使用以下命令再次获取相同的连接(SqlConnection)Connections.Connection.AcquireConnection(Transaction);：
    public override void AcquireConnections(object Transaction)
    {
        base.AcquireConnections(Transaction);
        conn = (SqlConnection)Connections.Connection.AcquireConnection(Transaction);
    }

在第一个脚本组件结束后，临时表已被删除，因为即使我不关闭连接，默认情况下连接似乎也会丢失。

寻求一种技巧，使我能够在许多 DFT 项以及整个包/项目中使用临时表

我希望找到一个答案，通过某种技巧使临时表保持活动状态。它必须只能在 SSIS 中工作，我不能只是在 SSMS 中创建临时表以使其在 SSIS 中保持活动状态。

如果 DFT 在留下任何 DFT 项后删除临时表，我如何在工作连接管理器中保持临时表处于活动状态，或者解决方法是什么？

sql-server

2 个回答

  1. Best Answer

    All you have to do is add an ADO.NET connection manager for SQL Server to your project and set it to "RetainSameConnection". Then each script task can access the same SqlConection object. eg

            var con = (SqlConnection) this.Connections.Connection.AcquireConnection(null); // Acquire the connection

        var cmd = con.CreateCommand();
        cmd.CommandText = "select 'helo temp table' msg into #foo";
        var msg = cmd.ExecuteNonQuery();
        
        this.Connections.Connection.ReleaseConnection(con); // Release the connection

    然后可以运行后续脚本

            var con = (SqlConnection)this.Connections.Connection.AcquireConnection(null); // Acquire the connection

        var cmd = con.CreateCommand();
        cmd.CommandText = "select top 1 msg from #foo"; 
        var msg = cmd.ExecuteScalar().ToString();

        this.Connections.Connection.ReleaseConnection(con); // Release the connection
    • 1

  2. 不是问题的答案

    这个答案是牵强的，显然不是答案，因为已经有一个可行的答案。这是留给搜索词的，因为我首先认为这个问题可能与控制流和 DFT 之间不一致的连接管理器有关。

    TL/DR

    这个答案不是问题的答案，而是留下后续问题的猜测：如何为数据流任务 - “脚本组件”创建一个连接管理器，可以为控制流选择 - “执行 SQL任务”也一样？我的猜测是，临时表将通过这样的全局和连接管理器保持活动状态。

    检查两种类型的连接管理器

    通过选择在“执行 SQL 任务”的控制流中建立连接<New_connection...>

    如果您通过选择以下方式在控制流中为“执行 SQL 任务”建立连接<New_connection...>：

    在此输入图像描述

    您可以在包级别获得带有“Provider”参数的连接字符串，例如Provider=SQLNCLI11.1：

    在此输入图像描述

    您这样创建的连接管理器有时会在数据流任务中引发错误：

    在此输入图像描述

    有一次在我的测试过程中，它抛出了错误：

    在此输入图像描述

    Severity    Code    Description Project File    Line    Suppression State
Error       Validation error. my_DFT_name my_item_name  [148]: System.ArgumentException: Keyword not supported: 'provider'.
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction)
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)      my_package.dtsx 0

    项目级连接管理器

    如果您在“解决方案资源管理器”窗格中在项目级别创建连接管理器，则无需“Provider”参数即可获得它：

    在此输入图像描述

    可以在 DFT 项目的菜单中选择它，而不会引发错误：

    在此输入图像描述

    没有错误：

    在此输入图像描述

    无法在控制流“执行 SQL 任务”中选择项目或本地范围的连接管理器

    您无法通过从下拉菜单中进行选择来选择项目级连接作为“执行 SQL 任务”的控制流中的连接管理器。奇怪的是，适用于脚本组件的连接管理器不会出现在控制流项的下拉列表中。对于“执行 SQL 任务”，即使我已经有“(project)tempdb”连接管理器，我也必须建立一个像“tempdb_pkg”这样的新连接：

    在此输入图像描述

    列表中没有“(project)tempdb”，我还使用从“连接管理器”窗格创建的连接管理器进行了尝试：

    在此输入图像描述

    I wonder why SSIS has this setup: A "tempdb" database connection manager in the project scope or in the local scope which is ready for the DFT Script Component dropdown menu cannot be taken as a connection manager for the Control Flow "Execute SQL Task".

    The answer which is just a guess

    My guess is now that if you find out how to make a connection manager that can be chosen in the dropdown menu of the "Execute SQL Task" and that is still a project level connection manager, such a connection manager might keep alive temporary tables. I do not know how to do this, and it seems to depend on the Provider parameter being there or not, but to me, this is enough of a hint for an answer here.

    Aim

    I want to deal with the same temporary table in both Control Flow and Data Flow Task (DFT), and it should never be dropped until the project or package has run through, and I need something that allows me to work with the temporary table in the DFT, since for the Control Flow alone, this is already working fine: with a connection manager for the "tempdb" database, and then I can make temporary tables in the Control Flow that survive the next step inside the Control Flow, see How to create a temporary table in SSIS control flow task and then use it in data flow task?.

    With a connection manager that can both be taken for the connection manager in the Control Flow for an "Execute SQL Task" by choosing <New_connection...> and for the Data Flow Task "Script Component", the temporary table might survive, just like it does inside the Control Flow.

    Wrapping up

    Up to now, in my tests, I could not find a way to not drop the temporary table once the first Script Component has run through, and I tried connections managers on all scopes.

    My hope is that a connection manager with some chosen arguments for perhaps optional parameters like Provider is not closed from one Script Component to the next, so that a temporary table that I saved with the first C# Script Component would still be alive after stepping over to the next Script Component in that same Data Flow Task.

    Any connection that is made from this Control Flow menu in the "Execute SQL Task" is not an ADO.NET connection, which is said to be needed for DFT. If one now could take the ADO.NET connection for both, that might fix it. I do not know how to do this, but it might fix it. Perhaps, BIML could enforce an ADO.NET connection manager for the Control Flow - "Execute SQL Task" even if it is not in the SSIS dropdown menu.

    PS

    These thoughts also feed the answer at System.ArgumentException: Keyword not supported: 'provider' - Stack Overflow. I do not know whether this is linked.

    • 0

相关问题