Determinar se os dados de alteração estão prontos

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

No fluxo de controle de um pacote do Integration Services que executa uma carga incremental de dados de alteração, a segunda tarefa é garantir que os dados de alteração para o intervalo selecionado estejam prontos. Esta etapa é necessária porque o processo de captura assíncrona pode ainda não ter processado todas as alterações até o ponto de extremidade selecionado.

Noções básicas sobre os componentes da solução

A solução descrita neste tópico usa 4 componentes do Integration Services:

• Um contêiner For Loop que avalia repetidamente a saída de uma tarefa Executar SQL.

• Uma tarefa Executar SQL que consulta tabelas especiais que o processo de captura de dados de alteração mantém e, em seguida, usa essas informações para determinar se os dados estão prontos.

• Um componente que implementa um atraso no processamento quando os dados não estão prontos. Isso pode ser uma tarefa Script ou uma tarefa de Executar SQL.

• Opcionalmente, um componente que relata um erro ou um tempo limite quando a tarefa Executar SQL retorna um valor que indica um erro ou uma condição de tempo limite.

Esses componentes definem ou leem os valores de várias variáveis de pacote para controlar o fluxo de execução dentro do loop e posteriormente no pacote.

Para configurar variáveis de pacote

1. No SSDT (SQL Server Data Tools), na janela Variáveis , crie as seguintes variáveis:

   1. Crie uma variável com um tipo de dados inteiro para manter o valor de status retornado pela tarefa Executar SQL.

      Este exemplo usa o nome da variável, DataReady, com um valor inicial de 0.

   2. Crie uma variável para manter o período de tempo para atrasar quando os dados não estiverem prontos. Se planeia usar uma tarefa Script para implementar o atraso, a variável deve ter o tipo de dados inteiro. Se você planeja usar uma tarefa Executar SQL com uma instrução WAITFOR, a variável deve ter um tipo de dados de cadeia de caracteres para aceitar valores como "00:00:10".

      Este exemplo usa o nome da variável, DelaySeconds, com um valor inicial de 10.

   3. Crie uma variável com um tipo de dados inteiro para manter a iteração atual do loop.

      Este exemplo usa o nome da variável, TimeoutCount, com um valor inicial de 0.

   4. Crie uma variável com um tipo de dados inteiro para especificar o número de vezes que o loop deve testar dados antes de relatar uma condição de tempo limite.

      Este exemplo usa o nome da variável, TimeoutCeiling, com um valor inicial de 20.

   5. (Opcional) Crie uma variável com um tipo de dados inteiro que você pode usar para indicar a primeira carga de dados de alteração.

      Este exemplo usa o nome da variável, IntervalID, e verifica apenas um valor de 0 para indicar a carga inicial.

Configurando um contêiner For Loop

Com as variáveis definidas, o contêiner For Loop é o primeiro componente a ser adicionado.

Para configurar um contêiner For Loop para aguardar até que os dados de alteração estejam prontos

1. Na guia Fluxo de Controle do Designer SSIS, adicione um contêiner For Loop ao fluxo de controle.

2. Conecte a tarefa Executar SQL que calcula os pontos de extremidade do intervalo ao contêiner For Loop.

3. No For Loop Editor, selecione as seguintes opções:

   1. Para InitExpression, digite @DataReady = 0.

      Esta expressão define o valor inicial da variável loop.

   2. Para EvalExpression, digite @DataReady == 0.

      Quando essa expressão é avaliada como False, a execução sai do loop e a carga incremental é iniciada.

Configurando a tarefa Executar SQL que consulta dados de alteração

Dentro do contêiner For Loop, adiciona-se uma tarefa de execução de SQL. Esta tarefa consulta as tabelas que o processo de captura de dados de alteração mantém no banco de dados. O resultado dessa consulta é um valor de status que indica se os dados de alteração estão prontos.

Na tabela a seguir, a primeira coluna mostra os valores retornados da tarefa Executar SQL pela consulta Transact-SQL de exemplo. A segunda coluna mostra como os outros componentes respondem a esses valores.

Para configurar uma tarefa de execução de SQL para consultar se os dados de mudanças estão prontos

1. Dentro do container For Loop, adicione uma tarefa de execução SQL.

2. No Editor de Tarefas Executar SQL, na página Geral , selecione as seguintes opções:

   1. Para ResultSet, selecione Linha única.

   2. Configure uma conexão válida com o banco de dados de origem.

   3. Para SQLSourceType, selecione Entrada direta.

   4. Para SQLStatement, insira a seguinte instrução SQL:

      declare @DataReady int, @TimeoutCount int  
      
      if not exists (select tran_end_time from cdc.lsn_time_mapping  
              where tran_end_time > ?  )  
          select @DataReady = 0  
      else  
          if ? = 0  
              select @DataReady = 3   
      else  
          if not exists (select tran_end_time from cdc.lsn_time_mapping  
                  where tran_end_time <= ? )  
              select @DataReady = 1   
      else  
          select @DataReady = 2  
      
      select @TimeoutCount = ?  
      if (@DataReady = 0)  
          select @TimeoutCount = @TimeoutCount + 1  
      else  
          select @TimeoutCount = 0  
      
      if (@TimeoutCount > ?)  
          select @DataReady = 5  
      
      select @DataReady as DataReady, @TimeoutCount as TimeoutCount  
      
      

3. Na página Mapeamento de Parâmetros do Editor de Tarefas Executar SQL, faça os seguintes mapeamentos:

   1. Mapeie a variável ExtractEndTime para o parâmetro 0.

   2. Mapeie a variável IntervalID para o parâmetro 1.

   3. Mapeie a variável ExtractStartTime para o parâmetro 2.

   4. Mapeie a variável TimeoutCount para o parâmetro 3.

   5. Mapeie a variável TimeoutCeiling para o parâmetro 4.

4. Na página Conjunto de Resultados do Editor de Tarefas de Execução SQL, associe o resultado DataReady à variável DataReady e o resultado TimeoutCount à variável TimeoutCount.

Aguardando até que os dados de alteração estejam prontos

Você pode usar um dos vários métodos para implementar um atraso quando os dados de alteração não estiverem prontos. Os seguintes dois procedimentos ilustram como usar uma tarefa de script ou uma tarefa de execução de SQL para efetuar o atraso.

Para implementar um atraso usando uma tarefa de script

1. Dentro do contêiner For Loop, adicione uma tarefa Script.

2. Conecte a tarefa Executar SQL que faz consultas para determinar se os dados de mudanças estão prontos à nova tarefa Script.

3. Para a restrição de precedência que conecta a tarefa Executar SQL à tarefa Script, abra o Editor de Restrição de Precedência e selecione as seguintes opções:

   1. Para Operação de avaliação, selecione Expressão e Restrição.

   2. Em Valor, selecione Sucesso.

      O valor de restrição de Sucesso refere-se ao sucesso da tarefa anterior. Nesse caso, o sucesso da tarefa de executar SQL.

   3. Em Expressão, digite @DataReady == 0 && @TimeoutCount <= @TimeoutCeiling.

   4. Selecione Lógico E. Todas as restrições devem ser avaliadas como True, se ainda não estiverem selecionadas.

4. No Editor de Tarefa de Script, na página Script, para ReadOnlyVariables, selecione a variável inteira User::DelaySeconds na lista.

5. No Editor de Tarefa de Script, na página Script, clique em Editar Script para abrir o ambiente de desenvolvimento de scripts.

6. No procedimento principal, insira uma das seguintes linhas de código:

   • Se você estiver programando em C#, digite a seguinte linha de código:

     System.Threading.Thread.Sleep((int)Dts.Variables["DelaySeconds"].Value * 1000);  
     

     - ou -

   • Se você estiver programando no Visual Basic, digite a seguinte linha de código:

     System.Threading.Thread.Sleep(Ctype(Dts.Variables("DelaySeconds").Value, Integer) * 1000)  
     
     

     Observação

     O método Thread.Sleep espera um argumento especificado em milissegundos.

7. Deixe a linha de código padrão que retorna DtsExecResult.Success da execução do script.

8. Feche o ambiente de desenvolvimento de scripts e o Editor de tarefas de script.

Para implementar um atraso usando uma tarefa Executar SQL

1. Dentro do container For Loop, adicione uma tarefa de execução SQL.

2. Conecte a tarefa Executar SQL que consulta para determinar se os dados de alteração estão prontos para a nova tarefa Executar SQL.

3. Para a restrição de precedência que conecta as duas tarefas Executar SQL, abra o Editor de Restrição de Precedência e selecione as seguintes opções:

   1. Para Operação de avaliação, selecione Expressão e Restrição.

   2. Em Valor, selecione Sucesso.

      O valor de restrição de Success refere-se ao sucesso da tarefa Executar SQL anterior.

   3. Em Expressão, digite @DataReady == 0.

   4. Selecione Lógico E. Todas as restrições devem ser avaliadas como True, se ainda não estiverem selecionadas.

      Esta seleção requer que ambas as condições, a restrição e a expressão, devem ser verdadeiras.

4. No Editor de Tarefas Executar SQL, na página Geral , selecione as seguintes opções:

   1. Para ResultSet, selecione Linha única.

   2. Configure uma conexão válida com o banco de dados de origem.

   3. Para SQLSourceType, selecione Entrada direta.

   4. Para SQLStatement, insira a seguinte instrução SQL:

      WAITFOR DELAY ?  
      
      

5. Na página Mapeamento de parâmetros do editor, mapeie a variável de cadeia de caracteres DelaySeconds para o parâmetro 0.

Lidar com uma condição de erro

Opcionalmente, você pode configurar um componente adicional dentro do loop para registrar um erro ou uma condição de tempo limite:

• Este componente pode registrar uma condição de erro quando o valor da variável DataReady = 1. Esse valor indica que não há dados de alteração disponíveis antes do início do intervalo selecionado.

• Esse componente também pode registrar uma condição de tempo limite quando o valor da variável TimeoutCeiling é atingido. Esse valor indica que o loop testou dados o número especificado de vezes e os dados ainda não estão disponíveis. Sem esse teste ou um teste semelhante, o pacote pode ser executado indefinidamente.

Para configurar uma tarefa de Script opcional para registrar uma condição de erro

1. Se você quiser relatar o erro ou o tempo limite gravando uma mensagem no log, configure o log para o pacote. Para obter mais informações, consulte Habilitar o log de pacotes no SQL Server Data Tools.

2. Dentro do contêiner For Loop, adicione uma tarefa Script.

3. Conecte a tarefa Executar SQL que faz consultas para determinar se os dados de mudanças estão prontos à nova tarefa Script.

4. Para a restrição de precedência que conecta a tarefa Executar SQL à tarefa Script, abra o Editor de Restrição de Precedência e selecione as seguintes opções:

   1. Para Operação de avaliação, selecione Expressão e Restrição.

   2. Em Valor, selecione Sucesso.

      O valor de restrição de Sucesso refere-se ao sucesso da tarefa anterior. Nesse caso, o sucesso da tarefa de executar SQL.

   3. Em Expressão, digite @DataReady == 1 || @DataReady == 5.

   4. Selecione Lógico E. Todas as restrições devem ser avaliadas como True, se ainda não estiverem selecionadas.

      Esta seleção requer que ambas as condições, a restrição e a expressão, devem ser verdadeiras.

5. No Editor de Tarefas de Script, na página Script do editor, para ReadOnlyVariables, selecione User::D ataReady e User::ExtractStartTime na lista para disponibilizar seus valores para o script.

   Se desejar incluir informações de determinadas variáveis do sistema (por exemplo, System::P ackageName) nas informações que você grava no log, selecione essas variáveis também.

6. No Editor de Tarefa de Script, na página Script, clique em Editar Script para abrir o ambiente de desenvolvimento de scripts.

7. No procedimento Main, insira o código para registrar um erro chamando o método Dts.Log ou para gerar um evento chamando um dos métodos da interface Dts.Events . Informe o pacote do erro retornando Dts.TaskResult = Dts.Results.Failure.

   O exemplo a seguir mostra como gravar uma mensagem no log. Para obter mais informações, consulte Registrando na tarefa de script, Gerando eventos na tarefa de script e Retornando resultados da tarefa de script.

   ' User variables.  
   Dim dataReady As Integer = _  
     CType(Dts.Variables("DataReady").Value, Integer)  
   Dim extractStartTime As Date = _  
     CType(Dts.Variables("ExtractStartTime").Value, DateTime)  
   
   ' System variables.  
   Dim packageName As String = _  
     Dts.Variables("PackageName").Value.ToString()  
   Dim executionStartTime As Date = _  
     CType(Dts.Variables("StartTime").Value, DateTime)  
   
   Dim eventMessage As New System.Text.StringBuilder()  
   
   If dataReady = 1 OrElse dataReady = 5 Then  
   
     If dataReady = 1 Then  
       eventMessage.AppendLine("Start Time Error")  
     Else  
       eventMessage.AppendLine("Timeout Error")  
     End If  
   
     With eventMessage  
       .Append("The package ")  
       .Append(packageName)  
       .Append(" started at ")  
       .Append(executionStartTime.ToString())  
       .Append(" and ended at ")  
       .AppendLine(DateTime.Now().ToString())  
       If dataReady = 1 Then  
         .Append("The specified ExtractStartTime was ")  
         .AppendLine(extractStartTime.ToString())  
       End If  
     End With  
   
     System.Windows.Forms.MessageBox.Show(eventMessage.ToString())  
   
     Dts.Log(eventMessage.ToString(), 0, Nothing)  
   
     Dts.TaskResult = Dts.Results.Failure  
   
   Else  
   
     Dts.TaskResult = Dts.Results.Success  
   
   End If  
   
   

8. Feche o ambiente de desenvolvimento de scripts e o Editor de tarefas de script.

Próximo Passo

Depois de determinar que os dados de alteração estão prontos, a próxima etapa é preparar-se para consultar os dados de alteração.

Próximo tópico:Preparar para consultar os dados de alteração