Carregando a saída de um pacote local

Artigo
05/15/2023

Aplica-se a:SQL Server SSIS Integration Runtime no Azure Data Factory

Aplicativos cliente podem ler a saída de pacotes do Integration Services quando a saída é salva em destinos do SQL Server por meio de ADO.NET ou quando a saída é salva em um destino de arquivo simples por meio das classes no namespace System.IO. Entretanto, um aplicativo cliente também consegue ler a saída de um pacote diretamente da memória, sem precisar de uma etapa intermediária para manter os dados. A resposta para essa solução é o namespace Microsoft.SqlServer.Dts.DtsClient, que contém implementações especializadas das interfaces IDbConnection, IDbCommand e IDbDataParameter do namespace System.Data. O assembly Microsoft.SqlServer.Dts.DtsClient.dll é instalado por padrão em %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn.

Importante

O procedimento descrito neste artigo, que usa a biblioteca DtsClient, funciona apenas para pacotes implantados com o modelo de implantação de pacote (ou seja, com a opção /SQL, /DTS ou /File). Esse procedimento não funciona para pacotes implantados com o modelo de implantação do servidor (ou seja, com a opção /ISServer). Para consumir a saída de um pacote local implantado com o modelo de implantação do servidor (ou seja, com a opção /ISServer), use o Destino do Streaming de Dados, em vez do procedimento descrito neste artigo.

Observação

O procedimento descrito neste tópico exige que a propriedade DelayValidation da tarefa Fluxo de Dados e de qualquer objeto pai seja definida com seu valor padrão False.

Descrição

Esse procedimento demonstra como desenvolver um aplicativo cliente em código gerenciado que carrega a saída de um pacote com um destino do DataReader diretamente da memória. As etapas resumidas aqui são demonstradas no código de exemplo que segue.

Para carregar a saída de pacote de dados em um aplicativo cliente

No pacote, configure um destino do DataReader para receber a saída que você quer ler no aplicativo cliente. Dê ao destino do DataReader um nome descritivo, pois você usará esse nome em seu aplicativo cliente posteriormente. Anote o nome do destino do DataReader.
No projeto de desenvolvimento, defina uma referência ao namespace Microsoft.SqlServer.Dts.DtsClient localizando o assembly Microsoft.SqlServer.Dts.DtsClient.dll. Por padrão, esse assembly é instalado em C:\Program Files\Microsoft SQL Server\100\DTS\Binn. Importe o namespace para o código usando a instrução Using do C# ou Visual Basic Imports do .
No código, crie um objeto do tipo DtsClient.DtsConnection com uma cadeia de conexão que contém os parâmetros de linha de comando necessários para que o dtexec.exe execute o pacote. Para saber mais, veja dtexec Utility. Em seguida, abra a conexão com essa cadeia de conexão. Também use o utilitário dtexecui para criar a cadeia de conexão necessária visualmente.

Observação

O código de exemplo demonstra o carregamento do pacote do sistema de arquivos usando a sintaxe /FILE <path and filename>. Contudo, você também pode carregar o pacote do banco de dados MSDB usando a sintaxe /SQL <package name> ou do repositório de pacotes do Integration Services usando a sintaxe /DTS \<folder name>\<package name>.
Crie um objeto do tipo DtsClient.DtsCommand que usa a DtsConnection criada anteriormente e defina sua propriedade CommandText com o nome do destino DataReader no pacote. Em seguida, chame o método ExecuteReader do objeto de comando para carregar os resultados do pacote em um novo DataReader.
Opcionalmente, parametrize indiretamente a saída do pacote usando a coleção de objetos DtsDataParameter no objeto DtsCommand para passar valores para variáveis definidas no pacote. No pacote, você pode usar essas variáveis como parâmetros de consulta ou em expressões para afetar os resultados retornados para o destino do DataReader. Você deve definir essas variáveis no pacote no namespace DtsClient antes que possa usá-las com o objeto DtsDataParameter em um aplicativo cliente. (Talvez você precise clicar no botão de barra de ferramentas Escolher Colunas de Variáveis na janela Variáveis para exibir a coluna Namespace.) No código cliente, ao adicionar um DtsDataParameter à coleção Parameters do DtsCommand, omita a referência ao namespace DtsClient do nome da variável. Por exemplo:
```
command.Parameters.Add(new DtsDataParameter("MyVariable", 1));  
```
Chame repetidamente o método Read do DataReader, conforme necessário, para executar um loop pelas linhas dos dados de saída. Use os dados ou salve-os para serem usados posteriormente no aplicativo cliente.

Importante

O método Read dessa implementação do DataReader retorna true mais uma vez depois que a última linha de dados é lida. Isso dificulta o uso do código comum que executa um loop no DataReader enquanto Read retorna true. Se o código tentar fechar o DataReader ou a conexão depois de ler o número esperado de linhas, sem uma chamada adicional final ao método Read, o código acionará uma exceção sem tratamento. Entretanto, se o código tentar ler dados nessa iteração final por meio de um loop, quando Read ainda retorna true, mas a última linha tiver passado, o código gerará uma ApplicationException sem tratamento com a mensagem “O IDataReader do SSIS passou do final do conjunto de resultados”. Esse comportamento é diferente de outras implementações do DataReader. Entretanto, ao usar um loop para ler as linhas no DataReader enquanto Read retorna true, você precisa escrever o código para capturar, testar e descartar essa ApplicationException antecipada na última chamada bem-sucedida ao método Read. Se preferir, caso saiba com antecedência o número de linhas esperado, você poderá processar as linhas e, em seguida, chamar o método Read mais uma vez antes de fechar o DataReader e a conexão.
Chame o método Dispose do objeto DtsCommand. Isso é particularmente importante se você usou objetos DtsDataParameter.
Feche o DataReader e os objetos de conexão.

Exemplo

O exemplo a seguir executa um pacote que calcula um único valor de agregação, salva esse valor em um destino do DataReader e o lê do DataReader e o exibe em uma caixa de texto em um formulário do Windows.

O uso de parâmetros não é necessário ao carregar a saída de um pacote em um aplicativo cliente. Se não desejar usar um parâmetro, omita o uso da variável no namespace DtsClient e omita o código que usa o objeto DtsDataParameter.

Para criar o pacote de teste

Crie um novo pacote do Integration Services. O código de exemplo usa "DtsClientWParamPkg.dtsx" como o nome do pacote.
Adicione uma variável do tipo Cadeia de caracteres ao namespace DtsClient. O código de exemplo usa País como nome da variável. (Talvez você precise clicar no botão de barra de ferramentas Escolher Colunas de Variáveis na janela Variáveis para exibir a coluna Namespace.)
Adicione um gerenciador de conexões do OLE DB que se conecta ao banco de dados de exemplo AdventureWorks2022.
Acrescente uma tarefa de fluxo de dados ao pacote e alterne para a superfície de design de Fluxo de Dados.
Adicione uma origem de OLE DB ao fluxo de dados e configure-a para usar o gerenciador de conexões OLE DB criado anteriormente, e o seguinte comando SQL:
```
SELECT * FROM Sales.vIndividualCustomer WHERE CountryRegionName = ?  
```
Clique em Parâmetros e, na caixa de diálogo Definir Parâmetros de Consulta, mapeie o único parâmetro de entrada na consulta, Parameter0, para a variável DtsClient::Country.
Acrescente uma transformação Agregação ao fluxo de dados e conecte a saída da origem de OLE DB à transformação. Abra o Editor de Transformação Agregação e configure-o para realizar uma operação “Contar todas” em todas as colunas de entrada (*) e gerar o valor agregado com o alias CustomerCount.
Acrescente um destino de DataReader ao fluxo de dados e conecte a saída da transformação Agregação a esse destino. O código de exemplo usa "DataReaderDest" como o nome do DataReader. Selecione a única coluna de entrada disponível, CustomerCount, para o destino.
Salve o pacote. O aplicativo de teste criado em seguida executará o pacote e recuperará sua saída diretamente da memória.

Para criar o aplicativo de teste

Crie um novo aplicativo Windows Forms.
Adicione uma referência ao namespace Microsoft.SqlServer.Dts.DtsClient procurando o assembly do mesmo nome em %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn.
Copie e cole o código de exemplo seguinte no módulo de código para o formulário.
Modifique o valor da variável dtexecArgs, conforme necessário, de forma que ela contenha os parâmetros de linha de comando necessários para que dtexec.exe execute o pacote. O código de exemplo carrega o pacote do sistema de arquivos.
Modifique o valor da variável dataReaderName, conforme necessário, de forma que ela contenha o nome do destino DataReader no pacote.
Coloque um botão e uma caixa de texto no formulário. O código de exemplo usa btnRun como o nome do botão e txtResults como o nome da caixa de texto.
Execute o aplicativo e clique no botão. Após uma pequena pausa, enquanto o pacote é executado, você deverá ver o valor de agregação calculado pelo pacote (a contagem de clientes no Canadá) exibido na caixa de texto do formulário.

Exemplo de código

Imports System.Data  
Imports Microsoft.SqlServer.Dts.DtsClient  
  
Public Class Form1  
  
  Private Sub btnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRun.Click  
  
    Dim dtexecArgs As String  
    Dim dataReaderName As String  
    Dim countryName As String  
  
    Dim dtsConnection As DtsConnection  
    Dim dtsCommand As DtsCommand  
    Dim dtsDataReader As IDataReader  
    Dim dtsParameter As DtsDataParameter  
  
    Windows.Forms.Cursor.Current = Cursors.WaitCursor  
  
    dtexecArgs = "/FILE ""C:\...\DtsClientWParamPkg.dtsx"""  
    dataReaderName = "DataReaderDest"  
    countryName = "Canada"  
  
    dtsConnection = New DtsConnection()  
    With dtsConnection  
      .ConnectionString = dtexecArgs  
      .Open()  
    End With  
  
    dtsCommand = New DtsCommand(dtsConnection)  
    dtsCommand.CommandText = dataReaderName  
  
    dtsParameter = New DtsDataParameter("Country", DbType.String)  
    dtsParameter.Direction = ParameterDirection.Input  
    dtsCommand.Parameters.Add(dtsParameter)  
  
    dtsParameter.Value = countryName  
  
    dtsDataReader = dtsCommand.ExecuteReader(CommandBehavior.Default)  
  
    With dtsDataReader  
      .Read()  
      txtResults.Text = .GetInt32(0).ToString("N0")  
    End With  
  
    'After reaching the end of data rows,  
    ' call the Read method one more time.  
    Try  
      dtsDataReader.Read()  
    Catch ex As Exception  
      MessageBox.Show("Exception on final call to Read method:" & ControlChars.CrLf & _  
      ex.Message & ControlChars.CrLf & _  
      ex.InnerException.Message, "Exception on final call to Read method", _  
      MessageBoxButtons.OK, MessageBoxIcon.Error)  
    End Try  
  
    ' The following method is a best practice, and is  
    '  required when using DtsDataParameter objects.  
    dtsCommand.Dispose()  
  
    Try  
      dtsDataReader.Close()  
    Catch ex As Exception  
      MessageBox.Show("Exception closing DataReader:" & ControlChars.CrLf & _  
      ex.Message & ControlChars.CrLf & _  
      ex.InnerException.Message, "Exception closing DataReader", _  
      MessageBoxButtons.OK, MessageBoxIcon.Error)  
    End Try  
  
    Try  
      dtsConnection.Close()  
    Catch ex As Exception  
      MessageBox.Show("Exception closing connection:" & ControlChars.CrLf & _  
      ex.Message & ControlChars.CrLf & _  
      ex.InnerException.Message, "Exception closing connection", _  
      MessageBoxButtons.OK, MessageBoxIcon.Error)  
    End Try  
  
    Windows.Forms.Cursor.Current = Cursors.Default  
  
  End Sub  
  
End Class

using System;  
using System.Windows.Forms;  
using System.Data;  
using Microsoft.SqlServer.Dts.DtsClient;  
  
namespace DtsClientWParamCS  
{  
  public partial class Form1 : Form  
  {  
    public Form1()  
    {  
      InitializeComponent();  
      this.btnRun.Click += new System.EventHandler(this.btnRun_Click);  
    }  
  
    private void btnRun_Click(object sender, EventArgs e)  
    {  
      string dtexecArgs;  
      string dataReaderName;  
      string countryName;  
  
      DtsConnection dtsConnection;  
      DtsCommand dtsCommand;  
      IDataReader dtsDataReader;  
      DtsDataParameter dtsParameter;  
  
      Cursor.Current = Cursors.WaitCursor;  
  
      dtexecArgs = @"/FILE ""C:\...\DtsClientWParamPkg.dtsx""";  
      dataReaderName = "DataReaderDest";  
      countryName = "Canada";  
  
      dtsConnection = new DtsConnection();  
      {  
        dtsConnection.ConnectionString = dtexecArgs;  
        dtsConnection.Open();  
      }  
  
      dtsCommand = new DtsCommand(dtsConnection);  
      dtsCommand.CommandText = dataReaderName;  
  
      dtsParameter = new DtsDataParameter("Country", DbType.String);  
      dtsParameter.Direction = ParameterDirection.Input;  
      dtsCommand.Parameters.Add(dtsParameter);  
  
      dtsParameter.Value = countryName;  
  
      dtsDataReader = dtsCommand.ExecuteReader(CommandBehavior.Default);  
  
      {  
        dtsDataReader.Read();  
        txtResults.Text = dtsDataReader.GetInt32(0).ToString("N0");  
      }  
  
      //After reaching the end of data rows,  
      // call the Read method one more time.  
      try  
      {  
        dtsDataReader.Read();  
      }  
      catch (Exception ex)  
      {  
        MessageBox.Show(  
          "Exception on final call to Read method:\n" + ex.Message + "\n" + ex.InnerException.Message,  
          "Exception on final call to Read method", MessageBoxButtons.OK, MessageBoxIcon.Error);  
      }  
  
      // The following method is a best practice, and is  
      //  required when using DtsDataParameter objects.  
      dtsCommand.Dispose();  
  
      try  
      {  
        dtsDataReader.Close();  
      }  
      catch (Exception ex)  
      {  
        MessageBox.Show(  
          "Exception closing DataReader:\n" + ex.Message + "\n" + ex.InnerException.Message,  
          "Exception closing DataReader", MessageBoxButtons.OK, MessageBoxIcon.Error);  
      }  
  
      try  
      {  
        dtsConnection.Close();  
      }  
      catch (Exception ex)  
      {  
        MessageBox.Show(  
          "Exception closing connection:\n" + ex.Message + "\n" + ex.InnerException.Message,  
          "Exception closing connection", MessageBoxButtons.OK, MessageBoxIcon.Error);  
      }  
  
      Cursor.Current = Cursors.Default;  
  
    }  
  }  
}

Consulte Também

Compreender as diferenças entre execução local e remota
Carregando e executando um pacote local programaticamente
Carregando e executando um pacote remoto programaticamente