Determinare se i dati delle modifiche sono pronti

Si applica a:SQL Server SSIS Integration Runtime in Azure Data Factory

Nel flusso di controllo di un pacchetto di Integration Services che esegue un caricamento incrementale dei dati delle modifiche, la seconda attività consiste nel verificare che i dati delle modifiche per l'intervallo selezionato siano pronti. Questo passaggio è necessario in quanto il processo di acquisizione asincrono potrebbe non avere ancora elaborato tutte le modifiche fino all'endpoint selezionato.

Informazioni sui componenti della soluzione

La soluzione illustrata in questo argomento usa 4 componenti di Integration Services:

• Un contenitore For Loop che valuta ripetutamente il risultato di un'attività di esecuzione SQL.

• Un'attività Execute SQL che esegue una query su tabelle speciali mantenute dal processo di Change Data Capture e utilizza quindi queste informazioni per determinare se i dati sono pronti.

• Un componente che implementa un ritardo nell'elaborazione quando i dati non sono pronti. Può trattarsi di un'attività "Script" o di un'attività "Esegui SQL".

• Facoltativamente, un componente che segnala un errore o un timeout quando l'attività Esegui SQL restituisce un valore indicante un errore o una condizione di timeout.

Tali componenti impostano o leggono i valori di numerose variabili del pacchetto per controllare il flusso di esecuzione all'interno del ciclo e successivamente nel pacchetto.

Per configurare le variabili del pacchetto

1. Nella finestra Variabili di SQL Server Data Tools (SSDT) creare le variabili seguenti:

   1. Creare una variabile con un tipo di dati integer per memorizzare il valore di stato restituito dall'attività Esegui SQL.

      In questo esempio viene utilizzato il nome di variabile DataReady con un valore iniziale pari a 0.

   2. Creare una variabile per memorizzare il periodo di tempo per il ritardo quando i dati non sono pronti. Se si intende utilizzare un'attività Script per implementare il ritardo, la variabile deve avere un tipo di dati intero. Se si prevede di utilizzare un'attività Esegui SQL con un'istruzione WAITFOR, la variabile deve avere un tipo di dati stringa per accettare valori quali "00.00.10".

      In questo esempio viene utilizzato il nome di variabile DelaySeconds con un valore iniziale pari a 10.

   3. Creare una variabile con un tipo di dati integer per memorizzare l'iterazione corrente del ciclo.

      In questo esempio viene utilizzato il nome di variabile TimeoutCount con un valore iniziale pari a 0.

   4. Creare una variabile con un tipo di dati integer per specificare il numero di volte in cui il ciclo deve eseguire il test dei dati prima di segnalare una condizione di timeout.

      In questo esempio viene utilizzato il nome di variabile TimeoutCeiling con un valore iniziale pari a 20.

   5. Creare una variabile con un tipo di dati integer che è possibile utilizzare per indicare il primo caricamento dei dati delle modifiche (facoltativo).

      In questo esempio viene utilizzato il nome di variabile IntervalID e viene verificata solo l'eventuale presenza di un valore pari a 0 per indicare il caricamento iniziale.

Configurazione del contenitore ciclo For

Dopo avere impostato le variabili, il contenitore Ciclo For rappresenta il primo componente da aggiungere.

Per configurare un contenitore For Loop in modo che attenda finché i dati delle modifiche non siano pronti

1. Nella scheda Flusso di controllo di SSIS Designer, aggiungere un contenitore For Loop nel flusso di controllo.

2. Connetti l'attività Esegui attività SQL che calcola i punti finali dell'intervallo al contenitore Ciclo For.

3. Nell'Editor del ciclo For selezionare le opzioni seguenti:

   1. Per InitExpression, immettere @DataReady = 0.

      Questa espressione imposta il valore iniziale della variabile del ciclo.

   2. Per EvalExpression immettere @DataReady == 0.

      Quando questa espressione viene valutata come False, l'esecuzione esce dal ciclo e ha inizio il caricamento incrementale.

Configurazione dell'attività Esegui SQL per l'esecuzione di una query per i dati delle modifiche

Nel contenitore Ciclo For, si aggiunge un'attività Esegui SQL. Questa attività interroga le tabelle gestite dal processo di Change Data Capture nel database. Il risultato della query è un valore di stato che indica se i dati delle modifiche sono pronti.

Nella tabella seguente la prima colonna indica i valori restituiti dall'attività Esegui SQL tramite la query Transact-SQL di esempio. La seconda colonna indica il modo in cui gli altri componenti rispondono a tali valori.

Per configurare un'attività Esegui SQL per eseguire una query che verifichi se i dati delle modifiche sono pronti

1. All'interno del contenitore Ciclo For, aggiungere un'attività Esegui SQL.

2. Nell'Editor attività Esegui SQL, nella pagina Generale, selezionare le opzioni seguenti:

   1. Per il ResultSet, selezionare Riga singola.

   2. Configurare una connessione valida al database di origine.

   3. Per SQLSourceType, selezionare Input diretto.

   4. Per SQLStatementimmettere l'istruzione SQL seguente:

      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. Nella pagina Mapping dei parametri dell'Editor dell'attività Esegui SQL, eseguire i mapping seguenti:

   1. Eseguire il mapping tra la variabile ExtractEndTime e il parametro 0.

   2. Associare la variabile IntervalID al parametro 1.

   3. Eseguire il mapping tra la variabile ExtractStartTime e il parametro 2.

   4. Associare la variabile TimeoutCount al parametro 3.

   5. Mappare la variabile TimeoutCeiling al parametro 4.

4. Nella pagina Set dei risultati dell'Editor attività Esegui SQL, associare il risultato DataReady alla variabile DataReady e il risultato TimeoutCount alla variabile TimeoutCount.

Attesa che i dati delle modifiche siano pronti

È possibile utilizzare uno dei numerosi metodi per implementare un ritardo quando i dati delle modifiche non sono pronti. Le due procedure seguenti illustrano come utilizzare un'attività Script o un'attività Execute SQL per implementare il ritardo.

Per creare un ritardo utilizzando un'attività Script

1. Aggiungere un'attività Script all'interno del contenitore For Loop.

2. Collegare la nuova attività Script all'attività Esegui SQL che interroga i dati per determinare se i dati relativi alle modifiche sono pronti.

3. Per il vincolo di precedenza che connette l'attività Esegui SQL all'attività Script, aprire Editor vincoli di precedenza e selezionare le opzioni seguenti:

   1. Per Operazione valutazione, selezionare Espressione e vincolo.

   2. Per Valore, selezionare Esito positivo.

      Il valore Esito positivo del vincolo si riferisce al risultato dell'attività precedente, In questo caso, l'esito positivo dell'attività Esegui SQL.

   3. Per Espressione, immettere @DataReady == 0 && @TimeoutCount <= @TimeoutCeiling.

   4. Selezionare AND logico. Se questa opzione non è già selezionata, tutti i vincoli devono restituire il valore True.

4. Nell'Editor attività Script, nella pagina Script, per ReadOnlyVariables, selezionare dall'elenco la variabile intera User::DelaySeconds.

5. Nell'Editor attività Script, nella pagina Script, fare clic su Modifica script per aprire l'ambiente di sviluppo script.

6. Nella routine Main immettere una delle righe di codice seguenti:

   • Se si utilizza il linguaggio di programmazione C#, immettere la riga di codice seguente:

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

     - o -

   • Se si usa il linguaggio di programmazione Visual Basic, immettere la riga di codice seguente:

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

     Nota

     Il metodo Thread.Sleep richiede un argomento espresso in millisecondi.

7. Lasciare la riga di codice predefinita, che restituisce DtsExecResult.Success dall'esecuzione dello script.

8. Chiudere l'ambiente di sviluppo dello script e l'Editor attività script.

Per implementare un ritardo utilizzando l'attività Esegui SQL

1. All'interno del contenitore Ciclo For, aggiungere un'attività Esegui SQL.

2. Connetti l'attività Esegui SQL che esegue la query per verificare se i dati di modifica sono pronti alla nuova attività Esegui SQL.

3. Per il vincolo di precedenza che connette le due attività Esegui SQL, aprire Editor vincoli di precedenza e selezionare le opzioni seguenti:

   1. Per Operazione valutazione, selezionare Espressione e vincolo.

   2. Per Valore, selezionare Esito positivo.

      Il valore Esito positivo del vincolo si riferisce al risultato dell'attività Esegui SQL precedente.

   3. Per Espressione, immettere @DataReady == 0.

   4. Selezionare AND logico. Se questa opzione non è già selezionata, tutti i vincoli devono restituire il valore True.

      Questa selezione richiede che entrambe le condizioni, il vincolo e l'espressione, siano vere.

4. Nell'Editor attività Esegui SQL, nella pagina Generale, selezionare le opzioni seguenti:

   1. Per il ResultSet, selezionare Riga singola.

   2. Configurare una connessione valida al database di origine.

   3. Per SQLSourceType, selezionare Input diretto.

   4. Per SQLStatementimmettere l'istruzione SQL seguente:

      WAITFOR DELAY ?  
      
      

5. Nella pagina Mappatura parametri dell'editor, associare la variabile di tipo stringa DelaySeconds al parametro 0.

Gestione di una condizione di errore

Se si desidera, è possibile configurare un componente aggiuntivo nel ciclo per registrare una condizione di errore o di timeout:

• Questo componente può registrare una condizione di errore quando il valore della variabile DataReady = 1. Questo valore indica che non sono disponibili dati delle modifiche prima dell'inizio dell'intervallo selezionato.

• Questo componente può registrare una condizione di timeout anche quando viene raggiunto il valore della variabile TimeoutCeiling. Questo valore indica che il ciclo ha testato i dati per il numero specificato di volte e i dati non sono ancora disponibili. Senza questo test o uno simile, l'esecuzione del pacchetto potrebbe continuare per un tempo illimitato.

Per configurare un'attività Script facoltativa per la registrazione di una condizione di errore

1. Se si desidera segnalare l'errore o il timeout scrivendo un messaggio nel log, configurare la registrazione per il pacchetto. Per altre informazioni, vedere Abilitare la registrazione di pacchetti in SQL Server Data Tools.

2. Aggiungere un'attività Script all'interno del contenitore For Loop.

3. Collegare la nuova attività Script all'attività Esegui SQL che interroga i dati per determinare se i dati relativi alle modifiche sono pronti.

4. Per il vincolo di precedenza che connette l'attività Esegui SQL all'attività Script, aprire Editor vincoli di precedenza e selezionare le opzioni seguenti:

   1. Per Operazione valutazione, selezionare Espressione e vincolo.

   2. Per Valore, selezionare Esito positivo.

      Il valore Esito positivo del vincolo si riferisce al risultato dell'attività precedente, In questo caso, l'esito positivo dell'attività Esegui SQL.

   3. Per Espressione, immettere @DataReady == 1 || @DataReady == 5.

   4. Selezionare AND logico. Se questa opzione non è già selezionata, tutti i vincoli devono restituire il valore True.

      Questa selezione richiede che entrambe le condizioni, il vincolo e l'espressione, siano vere.

5. Nell'Editor attività Script, nella pagina Script dell'editor, per ReadOnlyVariables, selezionare User::DataReady e User::ExtractStartTime dall'elenco per rendere i relativi valori disponibili per lo script.

   Se si desidera includere informazioni da determinate variabili di sistema, ad esempio System::PackageName, nelle informazioni scritte nel log, selezionare anche tali variabili.

6. Nell'Editor attività script, nella pagina Script, fare clic su Modifica script per aprire l'ambiente di sviluppo dello script.

7. Nella routine Main immettere il codice per registrare un errore chiamando il metodo Dts.Log o per generare un evento chiamando uno dei metodi dell'interfaccia Dts.Events . Informare dell'errore il pacchetto restituendo Dts.TaskResult = Dts.Results.Failure.

   Nell'esempio seguente viene illustrato come scrivere un messaggio nel log. Per altre informazioni, vedere Registrazione nell'attività Script, Generazione di eventi nell'attività Scripte Risultati restituiti dall'attività 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. Chiudere l'ambiente di sviluppo dello script e l'Editor attività script.

Passo successivo