Carregando a saída de um pacote local

Artigo
05/07/2013

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 chave 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.

Observação
O procedimento descrito neste tópico requer que a propriedade DelayValidation da tarefa Fluxo de Dados e de qualquer objeto pai seja definida com seu valor padrão, que é 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 com a localização do 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 em seu código usando o C# Using ou a instrução Visual Basic Imports.

Em seu código, crie um objeto do tipo DtsClient.DtsConnection com uma cadeia de conexão que contenha os parâmetros de linha de comando necessários para que o dtexec.exe execute o pacote. Para obter mais informações, consulte Utilitário dtexec. Em seguida, abra a conexão com essa cadeia de conexão. Você também pode usar 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 use o DtsConnection criado anteriormente e defina sua propriedade CommandText com o nome de destino do DataReader no pacote. Em seguida, chame o método ExecuteReader do objeto de comando para carregar os resultados do pacote em um DataReader novo.
Uma alternativa é parametrizar 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 para poder usá-las com o objeto DtsDataParameter de um aplicativo cliente. (Pode ser que você precise clicar no botão da barra de ferramentas Escolher Colunas de Variáveis na janela Variáveis para exibir a coluna Namespace). Em seu código de cliente, quando você adicionar um DtsDataParameter à coleção Parameters do DtsCommand, omita a referência do 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 o loop pelas linhas de 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 uma vez mais depois que a última linha de dados é lida. Isso dificulta o uso do código usual que executa o loop no DataReader enquanto Read retorna true. Se seu código tentar fechar o DataReader ou a conexão depois de ler o número de linhas esperado, sem uma chamada adicional final para o método Read, o código gerará uma exceção sem-tratamento. Entretanto, se seu código tentar ler dados nessa iteração final através 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 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 gravar o código para pegar, testar e descartar essa ApplicationException antecipada na última chamada bem sucedida do método Read. Ou, se você souber antes o número de linhas esperado, você poderá processar as linhas e depois chamar o método Read mais uma vez antes de fechar o DataReader e a conexão.

O método Read dessa implementação do DataReader retorna true uma vez mais depois que a última linha de dados é lida. Isso dificulta o uso do código usual que executa o loop no DataReader enquanto Read retorna true. Se seu código tentar fechar o DataReader ou a conexão depois de ler o número de linhas esperado, sem uma chamada adicional final para o método Read, o código gerará uma exceção sem-tratamento. Entretanto, se seu código tentar ler dados nessa iteração final através 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 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 gravar o código para pegar, testar e descartar essa ApplicationException antecipada na última chamada bem sucedida do método Read. Ou, se você souber antes o número de linhas esperado, você poderá processar as linhas e depois 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 qualquer objeto 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, você pode omitir o uso da variável no namespace DtsClient e omitir 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. (Pode ser que você precise clicar no botão da 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 AdventureWorks2012.
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 navegando até 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 requerido, de forma que ela contenha o nome do destino de 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.

Código de exemplo

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;

    }
  }
}

Fique atualizado com o Integration Services

Para obter os downloads, artigos, exemplos e vídeos da Microsoft mais recentes, bem como soluções selecionadas da comunidade, visite a página do Integration Services no MSDN: