Início / dba / Perguntas / 338395
Accepted
questionto42
Asked: 2024-04-08 01:30:57 +0800 CST

O DFT descarta a conexão e sua tabela temporária após deixar qualquer item DFT, testado com dois componentes de script. Como mantenho a tabela temporária ativa?

  • 772

Ligações

Isso ocupa:

A partir disso, pensei em testar como manter a conexão entre dois itens DFT para que a tabela temporária não fosse descartada.

O gerenciador de conexões Control Flow não corrige isso

Com um gerenciador de conexões para o banco de dados "tempdb", posso criar tabelas temporárias no Fluxo de Controle que sobrevivem à próxima etapa dentro do Fluxo de Controle, consulte Usar Tabela Temp no SSIS? . Embora você possa pensar que como criar uma tabela temporária na tarefa de fluxo de controle do SSIS e depois usá-la na tarefa de fluxo de dados? já responde a esta pergunta, descobri que a resposta não resolve o problema de tabelas temporárias descartadas após uma etapa no DFT.

O gerenciador de conexões ADO.NET não corrige isso

Testei o DFT com um gerenciador de conexões ADO.NET e a tabela temporária ainda foi descartada após o componente Script.

insira a descrição da imagem aqui

Configuração de teste

Testei em um Script Component que coloquei depois do outro:

insira a descrição da imagem aqui

Definir RetainSameConnectioncomo Truenão resolve

Eu configurei RetainSameConnectioncomo Truenos outros links, mas isso não resolveu.

Truques de código

Tentei não fazer uma nova conexão no segundo Componente Script, mas apenas adquiri-lo, sem sucesso:

    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();
    }
  • No Script Component 2, a mesma conexão é buscada novamente com (SqlConnection)Connections.Connection.AcquireConnection(Transaction);:
    public override void AcquireConnections(object Transaction)
    {
        base.AcquireConnections(Transaction);
        conn = (SqlConnection)Connections.Connection.AcquireConnection(Transaction);
    }

A tabela temporária é descartada já após o término do primeiro componente de script, pois a conexão parece ter sido perdida por padrão, mesmo que eu não a feche.

Busca por um truque que me permita trabalhar com tabelas temporárias em muitos itens DFT e em todo o pacote/projeto

Espero encontrar uma resposta que mantenha viva a tabela temporária com algum tipo de truque. Tudo deve funcionar apenas no SSIS, não posso simplesmente criar a tabela temporária no SSMS apenas para mantê-la ativa para o SSIS.

Se o DFT eliminar tabelas temporárias após deixar qualquer item DFT, como posso manter a tabela temporária ativa no gerenciador de conexões em funcionamento ou qual é a solução alternativa para isso?

sql-server

2 respostas

  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

    então um script subsequente pode ser executado

            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. Não é uma resposta para a pergunta

    Esta resposta é rebuscada e claramente não é a resposta, uma vez que já existe uma resposta funcional. Isso fica para as palavras de pesquisa, pois pensei inicialmente que essa pergunta poderia estar vinculada a um gerenciador de conexões que não está alinhado entre Control Flow e DFT.

    TL/DR

    Esta resposta não é uma resposta à pergunta, mas sim um palpite que deixa uma pergunta de acompanhamento: Como faço um gerenciador de conexões para a Tarefa Fluxo de Dados - "Componente Script" que pode ser escolhido para o Fluxo de Controle - "Executar SQL Tarefa" também? Meu palpite é que as tabelas temporárias permaneceriam vivas com esse gerenciador global e de conexões.

    Verificando dois tipos de gerenciadores de conexões

    Fazendo uma conexão no Fluxo de Controle para uma "Executar Tarefa SQL" escolhendo<New_connection...>

    Se você fizer uma conexão no Fluxo de Controle para uma "Executar Tarefa SQL" escolhendo <New_connection...>:

    insira a descrição da imagem aqui

    Você obtém uma string de conexão com o parâmetro "Provider" no nível do pacote, por exemplo com Provider=SQLNCLI11.1:

    insira a descrição da imagem aqui

    Um gerenciador de conexões que você criou assim às vezes gera um erro dentro da tarefa de fluxo de dados:

    insira a descrição da imagem aqui

    Uma vez, durante meus testes, ocorreu o erro:

    insira a descrição da imagem aqui

    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

    Gerenciador de conexões em nível de projeto

    Se, em vez disso, você criar um gerenciador de conexões no nível do projeto no painel "Solution Explorer", você o obterá sem o parâmetro "Provider":

    insira a descrição da imagem aqui

    Pode ser escolhido dentro do menu de itens do DFT sem gerar o erro:

    insira a descrição da imagem aqui

    Nenhum erro:

    insira a descrição da imagem aqui

    O gerenciador de conexões no projeto ou no escopo local não pode ser escolhido no Fluxo de Controle "Executar Tarefa SQL"

    You cannot choose the project level connection as the connection manager in the Control Flow for an "Execute SQL Task" by choosing from the dropdown menu. Strangely, a connection manager that works for the Script Component would not be in the dropdown list of a Control Flow item. For the "Execute SQL Task", I would have to make a new connection like "tempdb_pkg" even if I already have a "(project)tempdb" connection manager:

    insira a descrição da imagem aqui

    There is no "(project)tempdb" in the list, and I also tried it with a connection manager that I made from the "Connection Managers" pane:

    insira a descrição da imagem aqui

    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

relate perguntas