Bepalen of de wijzigingsgegevens gereed zijn

van toepassing op:SQL Server SSIS Integration Runtime in Azure Data Factory

In de controlestroom van een Integration Services-pakket dat een incrementele belasting van wijzigingsgegevens uitvoert, moet de tweede taak ervoor zorgen dat de wijzigingsgegevens voor het geselecteerde interval gereed zijn. Deze stap is nodig omdat het asynchrone opnameproces mogelijk nog niet alle wijzigingen tot het geselecteerde eindpunt heeft verwerkt.

Inzicht in de onderdelen van de oplossing

De oplossing die in dit onderwerp wordt beschreven, maakt gebruik van 4 Integration Services-onderdelen:

• Een For Loop-container die herhaaldelijk de uitvoer van een Execute SQL-taak evalueert.

• Een SQL-taak uitvoeren waarmee query's worden uitgevoerd op speciale tabellen die door het proces voor het vastleggen van wijzigingen worden onderhouden en vervolgens wordt deze informatie gebruikt om te bepalen of gegevens gereed zijn.

• Een onderdeel dat een vertraging in de verwerking implementeert wanneer de gegevens niet gereed zijn. Dit kan een scripttaak of een SQL-taak uitvoeren zijn.

• Optioneel kan een onderdeel een fout of een time-out rapporteren wanneer de SQL-taak Uitvoeren een waarde retourneert die wijst op een fout of een time-outconditie.

Deze onderdelen stellen de waarden van verschillende pakketvariabelen in of lezen om de uitvoeringsstroom binnen de lus en later in het pakket te beheren.

Instellen van pakketvariabelen

1. Maak in SQL Server Data Tools (SSDT) in het venster Variabelen de volgende variabelen:

   1. Maak een variabele met het gegevenstype 'integer' om de statuswaarde te bewaren die wordt geretourneerd door de Execute SQL-taak.

      In dit voorbeeld wordt de naam van de variabele, DataReady, gebruikt met de initiële waarde 0.

   2. Maak een variabele om de tijdsperiode vast te stellen die moet worden gewacht wanneer gegevens niet gereed zijn. Als u van plan bent om een Script-taak te gebruiken om de vertraging te implementeren, moet de variabele van het gegevenstype integer zijn. Als u van plan bent om een SQL-taak uitvoeren met een WAITFOR-instructie te gebruiken, moet de variabele een gegevenstype tekenreeks hebben om waarden te accepteren, zoals '00:00:10'.

      In dit voorbeeld wordt de naam van de variabele, DelaySeconds, met een initiële waarde van 10 gebruikt.

   3. Maak een variabele van het gegevenstype integer om de huidige iteratie van de lus vast te houden.

      In dit voorbeeld wordt de naam van de variabele, TimeoutCount, gebruikt met een initiële waarde van 0.

   4. Maak een variabele met een integer gegevenstype om het aantal keren op te geven dat de lus moet testen op gegevens voordat een timeoutvoorwaarde wordt gerapporteerd.

      In dit voorbeeld wordt de variabelenaam TimeoutCeiling gebruikt met een initiële waarde van 20.

   5. (Optioneel) Maak een variabele met een gegevenstype geheel getal dat u kunt gebruiken om de eerste belasting van wijzigingsgegevens aan te geven.

      In dit voorbeeld wordt de variabelenaam IntervalID gebruikt en wordt er alleen gecontroleerd op een waarde van 0 om de oorspronkelijke lading aan te geven.

Een For Loop-container configureren

Met de variabelen die zijn ingesteld, is de For Loop-container het eerste onderdeel dat moet worden toegevoegd.

Een For Loop-container configureren om te wachten totdat de wijzigingsgegevens gereed zijn.

1. Voeg op het tabblad Controlestroom van SSIS Designer een For Loop-container toe aan de besturingsstroom.

2. Verbind de SQL-taak uitvoeren waarmee de eindpunten van het interval worden berekend met de For Loop-container.

3. Selecteer in de For Loop Editor de volgende opties:

   1. Voer voor InitExpression de tekst in @DataReady = 0.

      Met deze expressie wordt de initiële waarde van de lusvariabele ingesteld.

   2. Voer voor EvalExpression het in @DataReady == 0.

      Wanneer deze expressie evalueert naar false, verlaat de uitvoering de lus en begint het incrementeel laden.

De SQL-taak configureren voor het uitvoeren van query's op wijzigingsgegevens

In de For Loop-container voegt u een SQL-taak uitvoeren toe. Met deze taak worden de tabellen opgevraagd die door het proces voor het vastleggen van wijzigingen in de database worden onderhouden. Het resultaat van deze query is een statuswaarde die aangeeft of de wijzigingsgegevens gereed zijn.

In de volgende tabel laat de eerste kolom de waarden zien die zijn geretourneerd door de voorbeeldquery Transact-SQL in de Execute SQL-taak. In de tweede kolom ziet u hoe de andere onderdelen op deze waarden reageren.

Om een Execute SQL-taak te configureren waarmee onderzocht kan worden of wijzigingsgegevens gereed zijn

1. Voeg in de For Loop-container een SQL-taak uitvoeren toe.

2. Selecteer in de sql-taakeditor uitvoeren op de pagina Algemeen de volgende opties:

   1. Selecteer voor ResultSetéén rij.

   2. Configureer een geldige verbinding met de brondatabase.

   3. Voor SQLSourceType selecteert u Directe invoer.

   4. Voer voor SQLStatement de volgende SQL-instructie in:

      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. Voer de volgende toewijzingen uit op de pagina Parametertoewijzing van de SQL-taakeditor uitvoeren:

   1. Wijs de variabele ExtractEndTime toe aan parameter 0.

   2. Wijs de variabele IntervalID toe aan parameter 1.

   3. Wijs de variabele ExtractStartTime toe aan parameter 2.

   4. Wijs de variabele TimeoutCount toe aan parameter 3.

   5. Wijs de variabele TimeoutCeiling toe aan parameter 4.

4. Wijs op de pagina Resultatenset van de SQL-taakeditor Uitvoeren het DataReady-resultaat toe aan de variabele DataReady en het TimeoutCount-resultaat aan de variabele TimeoutCount.

Wachten totdat de wijzigingsgegevens gereed zijn

U kunt een van de verschillende methoden gebruiken om een vertraging te implementeren wanneer de wijzigingsgegevens niet gereed zijn. De volgende twee procedures laten zien hoe u een Script-taak of een SQL-taak gebruikt om de vertraging te realiseren.

Een vertraging implementeren met behulp van een scripttaak

1. Voeg in de container For Loop een scripttaak toe.

2. Verbind de SQL-taak Uitvoeren die query's uitvoert om te bepalen of de wijzigingsgegevens gereed zijn voor de nieuwe scripttaak.

3. Voor de prioriteitsbeperking die de SQL-taak uitvoeren verbindt met de scripttaak, opent u de editor prioriteitsbeperking en selecteert u de volgende opties:

   1. Voor evaluatiebewerking selecteert u Expressie en Beperking.

   2. Voor Waarde, selecteer Succes.

      De beperkingswaarde van Geslaagd verwijst naar het succes van de vorige taak. In dit geval is het succes van de taak SQL Uitvoeren.

   3. Voor Uitdrukking, voer @DataReady == 0 && @TimeoutCount <= @TimeoutCeiling in.

   4. Selecteer Logische EN. Alle beperkingen moeten waar zijn als deze nog niet zijn geselecteerd.

4. Selecteer in de Script Task Editor op de pagina Script voor ReadOnlyVariables de integervariabele User::DelaySeconds uit de lijst.

5. Klik in de scripttaakeditor op de pagina Script op Script bewerken om de ontwikkelomgeving voor scripts te openen.

6. Voer in de hoofdprocedure een van de volgende coderegels in:

   • Als u in C# programmeert, voert u de volgende coderegel in:

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

     - of -

   • Als u programmeert in Visual Basic, voert u de volgende coderegel in:

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

     Opmerking

     De thread.Sleep-methode verwacht een argument dat is opgegeven in milliseconden.

7. Laat de standaardcoderegel staan die DtsExecResult.Success retourneert bij de uitvoering van het script.

8. Sluit de ontwikkelomgeving van het script en de scripttaakeditor.

Het implementeren van een vertraging met behulp van een taak 'SQL uitvoeren'.

1. Voeg in de For Loop-container een SQL-taak uitvoeren toe.

2. Verbind de taak "SQL Uitvoeren" die nagaat of de veranderingsgegevens gereed zijn, met de nieuwe taak "SQL Uitvoeren".

3. Voor de prioriteitsbeperking waarmee de twee SQL-taken worden uitgevoerd, opent u de editor prioriteitsbeperking en selecteert u de volgende opties:

   1. Voor evaluatiebewerking selecteert u Expressie en Beperking.

   2. Voor Waarde, selecteer Succes.

      De beperkingswaarde van Geslaagd verwijst naar het succes van de vorige SQL-taak uitvoeren.

   3. Voor Uitdrukking, voer @DataReady == 0 in.

   4. Selecteer Logische EN. Alle beperkingen moeten waar zijn als deze nog niet zijn geselecteerd.

      Voor deze selectie moeten beide voorwaarden, de beperking en de expressie, waar zijn.

4. Selecteer in de sql-taakeditor uitvoeren op de pagina Algemeen de volgende opties:

   1. Selecteer voor ResultSetéén rij.

   2. Configureer een geldige verbinding met de brondatabase.

   3. Voor SQLSourceType selecteert u Directe invoer.

   4. Voer voor SQLStatement de volgende SQL-instructie in:

      WAITFOR DELAY ?  
      
      

5. Wijs op de pagina Parametertoewijzing van de editor de tekenreeksvariabele DelaySeconds toe aan parameter 0.

Een foutsituatie oplossen

U kunt eventueel een extra onderdeel in de lus configureren om een fout of een time-out conditie te loggen.

• Dit onderdeel kan een foutvoorwaarde vastleggen wanneer de waarde van de DataReady-variabele = 1. Deze waarde geeft aan dat er vóór het begin van het geselecteerde interval geen gegevens beschikbaar zijn.

• Dit onderdeel kan ook een time-outvoorwaarde vastleggen wanneer de waarde van de variabele TimeoutCeiling is bereikt. Deze waarde geeft aan dat de lus is getest op gegevens het opgegeven aantal keren en dat de gegevens nog steeds niet beschikbaar zijn. Zonder deze test of een vergelijkbare test kan het pakket voor onbepaalde tijd worden uitgevoerd.

Een optionele scriptopdracht configureren om een foutsituatie vast te leggen

1. Als u de fout of time-out wilt rapporteren door een bericht naar het logboek te schrijven, configureert u logboekregistratie voor het pakket. Zie Logboekregistratie van pakketten inschakelen in SQL Server Data Tools voor meer informatie.

2. Voeg in de container For Loop een scripttaak toe.

3. Verbind de SQL-taak Uitvoeren die query's uitvoert om te bepalen of de wijzigingsgegevens gereed zijn voor de nieuwe scripttaak.

4. Voor de prioriteitsbeperking die de SQL-taak uitvoeren verbindt met de scripttaak, opent u de editor prioriteitsbeperking en selecteert u de volgende opties:

   1. Voor evaluatiebewerking selecteert u Expressie en Beperking.

   2. Voor Waarde, selecteer Succes.

      De beperkingswaarde van Geslaagd verwijst naar het succes van de vorige taak. In dit geval is het succes van de taak SQL Uitvoeren.

   3. Voor Uitdrukking, voer @DataReady == 1 || @DataReady == 5 in.

   4. Selecteer Logische EN. Alle beperkingen moeten waar zijn als deze nog niet zijn geselecteerd.

      Voor deze selectie moeten beide voorwaarden, de beperking en de expressie, waar zijn.

5. Selecteer in de scripttaakeditor, op de scriptpagina van de editor, voor ReadOnlyVariables, User::D ataReady en User::ExtractStartTime uit de lijst om hun waarden beschikbaar te maken voor het script.

   Als u informatie uit bepaalde systeemvariabelen (bijvoorbeeld System::P ackageName) wilt opnemen in de informatie die u naar het logboek schrijft, selecteert u deze variabelen ook.

6. Klik in de scripttaakeditor op de pagina Script op Script bewerken om de ontwikkelomgeving voor scripts te openen.

7. Voer in de hoofdprocedure code in om een fout te registreren door de methode Dts.Log aan te roepen of om een gebeurtenis te genereren door een van de methoden van de Dts.Events-interface aan te roepen. Informeer de applicatie over de fout door Dts.TaskResult = Dts.Results.Failure terug te geven.

   In het volgende voorbeeld ziet u hoe u een bericht naar het logboek schrijft. Zie Logboekregistratie in de scripttaak, het genereren van gebeurtenissen in de scripttaak en het retourneren van resultaten van de scripttaak voor meer informatie.

   ' 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. Sluit de ontwikkelomgeving van het script en de scripttaakeditor.

Volgende stap

Nadat u hebt vastgesteld dat wijzigingsgegevens gereed zijn, is de volgende stap het voorbereiden om een query uit te voeren op de wijzigingsgegevens.

Volgend onderwerp:Voorbereiden om query's uit te voeren voor de wijzigingsgegevens