Vorbereiten zur Abfrage der Änderungsdaten

  • Artikel

Gilt für:SQL Server SSIS Integration Runtime in Azure Data Factory

Im Kontrollfluss eines Integration Services-Pakets, das ein inkrementelles Laden von Änderungsdaten durchführt, besteht die dritte und letzte Aufgabe darin, die Abfrage der Änderungsdaten vorzubereiten und eine Datenflussaufgabe hinzuzufügen.

Hinweis

Beim zweiten Task für die Ablaufsteuerung muss sichergestellt werden, dass die Änderungsdaten für das ausgewählte Intervall bereit sind. Weitere Informationen zu diesem Task finden Sie unter Bestimmen, ob die Änderungsdaten bereit sind. Eine Beschreibung des Gesamtprozesses zum Entwurf der Ablaufsteuerung finden Sie unter Change Data Capture (SSIS).

Entwurfsaspekte

Wenn Sie die Änderungsdaten abrufen möchten, müssen Sie eine Transact-SQL-Tabellenwertfunktion aufrufen, die die Endpunkte des Intervalls als Eingabeparameter akzeptiert und für das angegebene Intervall Änderungsdaten zurückgibt. Eine Quellkomponente im Datenfluss ruft diese Funktion auf. Weitere Informationen zu dieser Quellkomponente finden Sie unter Abrufen und Verstehen der Änderungsdaten.

Die am häufigsten verwendeten Quellkomponenten der Integrationsdienste, darunter die OLE DB-Quelle, die ADO-Quelle und die ADO NET-Quelle, können keine Parameterinformationen für eine tabellenwertige Funktion ableiten. Deshalb können die meisten Quellen eine parametrisierte Funktion nicht direkt aufrufen.

Ihnen stehen zwei Entwurfsoptionen zur Verfügung, um die Eingabeparameter an die Funktion zu übergeben:

  • Assemblieren Sie die parametrisierte Abfrage als Zeichenfolge. Sie können einen Skripttask oder einen Task "SQL ausführen" verwenden, um eine dynamische SQL-Zeichenfolge mit Parameterwerten hartcodiert in die Zeichenfolge zu assemblieren. Sie können dann diese Zeichenfolge in einer Paketvariablen speichern und diese verwenden, um die SqlCommand-Eigenschaft einer Quellkomponente festzulegen. Dieser Ansatz ist erfolgreich, da die Quellkomponente nicht länger Parameterinformationen benötigt.

    Hinweis

    Ein vorkompiliertes Skript verursacht weniger Aufwand als ein Task "SQL ausführen".

  • Verwenden Sie einen parametrisierten Wrapper. Alternativ können Sie eine parametrisierte gespeicherte Prozedur als Wrapper erstellen, der die parametrisierte Tabellenwertfunktion aufruft. Dieser Ansatz ist erfolgreich, da eine Quellkomponente Parameterinformationen für eine gespeicherte Prozedur erfolgreich ableiten kann.

Dieses Thema verwendet die erste Entwurfsoption und assembliert eine parametrisierte Abfrage als Zeichenfolge.

Vorbereiten der Abfrage

Bevor Sie die Werte der Eingabeparameter in eine einzelne Abfragezeichenfolge verketten können, müssen Sie die für die Abfrage erforderlichen Paketvariablen einrichten.

So richten Sie Paketvariablen ein

  • Erstellen Sie in den SQL Server Data Tools (SSDT) im Fenster Variablen eine Variable mit dem Datentyp String, um die von der Aufgabe SQL ausführen zurückgegebene Abfragezeichenfolge zu speichern.

    In diesem Beispiel wird der Variablenname "SqlDataQuery" verwendet.

Wenn die Paketvariable erstellt wurde, können Sie einen Skripttask oder einen Task "SQL ausführen" verwenden, um die Werte der Eingabeparameter zu verketten. Die folgenden zwei Prozeduren beschreiben, wie diese Komponenten konfiguriert werden.

So verwenden Sie einen Skripttask zur Verkettung der Abfragezeichenfolge

  1. Fügen Sie dem Paket auf der Registerkarte Ablaufsteuerung nach dem For-Schleifencontainer einen Skripttask hinzu, und verbinden Sie den For-Schleifencontainer mit dem Task.

    Hinweis

    In dieser Prozedur wird davon ausgegangen, dass das Paket ein inkrementelles Laden aus einer einzelnen Tabelle ausführt. Wenn das Paket aus mehreren Tabellen lädt und ein übergeordnetes Paket mit mehreren untergeordneten Paketen hat, würde dieser Task jedem untergeordneten Paket als erste Komponente hinzugefügt werden. Weitere Informationen finden Sie unter Ausführen eines inkrementellen Ladens von mehreren Tabellen.

  2. Aktivieren Sie im Skripttask-Editorauf der Seite Skript die folgenden Optionen:

    1. Wählen Sie für ReadOnlyVariablesdie Optionen User::DataReady, User::ExtractStartTimeund User::ExtractEndTime aus.

    2. Wählen Sie für ReadWriteVariablesdie Option User::SqlDataQuery aus der Liste aus.

  3. Klicken Sie im Skripttask-Editorauf der Seite Skript auf Skript bearbeiten , um die Skriptentwicklungsumgebung zu öffnen.

  4. Geben Sie in der Main-Prozedur eines der folgenden Codesegmente ein:

    • Wenn Sie in C# programmieren, geben Sie die folgenden Codezeilen ein:

      int dataReady;  
System.DateTime extractStartTime;  
System.DateTime extractEndTime;  
string sqlDataQuery;  

dataReady = (int)Dts.Variables["DataReady"].Value;  
extractStartTime = (System.DateTime)Dts.Variables["ExtractStartTime"].Value;  
extractEndTime = (System.DateTime)Dts.Variables["ExtractEndTime"].Value;  

if (dataReady == 2)  
  {  
  sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer('" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", extractStartTime) + "', '" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) + "')";  
  }  
else  
  {  
  sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer(null" + ", '" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) + "')";  
  }  

Dts.Variables["SqlDataQuery"].Value = sqlDataQuery;

      - oder -

    • Wenn Sie in Visual Basic programmieren, geben Sie die folgenden Codezeilen ein:

      Dim dataReady As Integer  
Dim extractStartTime As Date  
Dim extractEndTime As Date  
Dim sqlDataQuery As String  

dataReady = CType(Dts.Variables("DataReady").Value, Integer)  
extractStartTime = CType(Dts.Variables("ExtractStartTime").Value, Date)  
extractEndTime = CType(Dts.Variables("ExtractEndTime").Value, Date)  

If dataReady = 2 Then  
  sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer('" & _  
      String.Format("{0:yyyy-MM-dd hh:mm:ss}", extractStartTime) & _  
      "', '" & _  
      String.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) & _  
      "')"  
Else  
  sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer(null" & _  
      ", '" & _  
      String.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) & _  
      "')"  
End If  

Dts.Variables("SqlDataQuery").Value = sqlDataQuery

  5. Verlassen Sie die Standardcodezeile, die DtsExecResult.Success aus der Ausführung des Skripts zurückgibt.

  6. Schließen Sie die Skriptentwicklungsumgebung und den Skripttask-Editor.

So verwenden Sie einen Task "SQL ausführen" zur Verkettung der Abfragezeichenfolge

  1. Fügen Sie dem Paket auf der Registerkarte Ablaufsteuerung nach dem For-Schleifencontainer einen Task "SQL ausführen" hinzu, und verbinden Sie den For-Schleifencontainer mit diesem Task.

    Hinweis

    In dieser Prozedur wird davon ausgegangen, dass das Paket ein inkrementelles Laden aus einer einzelnen Tabelle ausführt. Wenn das Paket aus mehreren Tabellen lädt und ein übergeordnetes Paket mit mehreren untergeordneten Paketen hat, würde dieser Task jedem untergeordneten Paket als erste Komponente hinzugefügt werden. Weitere Informationen finden Sie unter Ausführen eines inkrementellen Ladens von mehreren Tabellen.

  2. Aktivieren Sie im Skripttask-Editorauf der Seite Skript die folgenden Optionen:

    1. Wählen Sie für ResultSetdie Option Einzelne Zeileaus.

    2. Konfigurieren Sie zur Quelldatenbank eine gültige Verbindung.

    3. Wählen Sie für SQLSourceTypedie Option Direkteingabeaus.

    4. Geben Sie für SQLStatementdie folgende SQL-Anweisung ein:

      declare @ExtractStartTime datetime,  
@ExtractEndTime datetime,   
@DataReady int  

select @DataReady = ?,   
@ExtractStartTime = ?,   
@ExtractEndTime = ?  

if @DataReady = 2  
select N'select * from CDCSample.uf_Customer'  
+ N'('''+ convert(nvarchar(30),@ExtractStartTime,120)  
+ ''', '''  
+ convert(nvarchar(30),@ExtractEndTime,120) + ''') '   
as SqlDataQuery  
else  
select N'select * from CDCSample.uf_Customer'  
+ N'(null, '''  
+ convert(nvarchar(30),@ExtractEndTime,120)  
+ ''') '  
as SqlDataQuery

      Hinweis

      Die else -Klausel in diesem Beispiel generiert eine Abfrage für das erste Laden der Änderungsdaten, indem für das Startdatum und die Startzeit ein NULL-Wert übergeben wird. Dieses Beispiel befasst sich nicht mit dem Szenario, in dem Änderungen, die vor der Aktivierung von Change Data Capture vorgenommen wurden, auch ins Data Warehouse hochgeladen werden müssen.

  3. Nehmen Sie auf der Seite Parameterzuordnung vom Editor für den Task 'SQL ausführen'die folgende Zuordnung vor:

    1. Ordnen Sie dem Parameter 0 die DataReady-Variable zu.

    2. Ordnen Sie dem Parameter 1 die ExtractStartTime-Variable zu.

    3. Ordnen Sie dem Parameter 2 die ExtractEndTime-Variable zu.

  4. Ordnen Sie auf der Seite Resultset vom Editor für den Task 'SQL ausführen'der SqlDataQuery-Variablen den Ergebnisnamen zu.

    Der Ergebnisname ist der Name der einzelnen Spalte, die zurückgegeben wird, SqlDataQuery.

Die oben beschriebenen Prozeduren konfigurieren einen Task, der eine Abfragezeichenfolge mit hartcodierten Zeichenfolgewerten für die Eingabeparameter vorbereitet. Der folgende Code ist ein Beispiel für eine solche Abfragezeichenfolge:

select * from CDCSample. uf_Customer('2007-06-11 14:21:58', '2007-06-12 14:21:58')

Hinzufügen eines Datenflusstasks

Das Hinzufügen eines Datenflusstasks ist der letzte Schritt beim Entwerfen einer Ablaufsteuerung für ein Paket.

So fügen Sie einen Datenflusstask hinzu und vervollständigen Sie die Ablaufsteuerung

  • Fügen Sie auf der Registerkarte Ablaufsteuerung einen Datenflusstask hinzu, und verbinden Sie den Task, der die Abfragezeichenfolge verkettet hat.

Nächster Schritt

Nach der Vorbereitung der Abfragezeichenfolge und der Konfiguration des Datenflusstasks besteht der nächste Schritt darin, die Tabellenwertfunktion zu erstellen, mit der die Änderungsdaten von der Datenbank abgerufen werden.

Nächstes Thema:Erstellen der Funktion zum Abrufen der Änderungsdaten