Przygotowywanie do wykonywania zapytań dotyczących zmian danych

Dotyczy:SQL Server Środowisko SSIS Integration Runtime w usłudze Azure Data Factory

W przepływie sterowania pakietu Integration Services, który wykonuje przyrostowe ładowanie danych o zmianach, trzecim i ostatnim zadaniem jest przygotowanie do wykonywania zapytań o dane o zmianach oraz dodanie zadania Przepływ danych.

Zagadnienia dotyczące projektowania

Aby pobrać dane zmiany, wywołasz funkcję tabelową Transact-SQL, która akceptuje końcowe punkty przedziału jako parametry wejściowe i zwraca dane zmiany dla określonego przedziału. Komponent źródłowy w przepływie danych wywołuje tę funkcję. Aby uzyskać informacje o tym komponentie źródłowym, zobacz Pobierz i zrozum dane zmiany.

Najczęściej używane komponenty źródłowe Integration Services, w tym źródło OLE DB, ADO oraz ADO NET, nie potrafią wyprowadzić informacji parametrów dla funkcji tabelowej. Dlatego większość źródeł nie może bezpośrednio wywołać funkcji parametryzowanej.

Masz dwie opcje projektowania przekazywania parametrów wejściowych do funkcji:

• Złóż parametryzowane zapytanie jako ciąg znaków. Możesz użyć zadania skryptowego lub zadania Execute SQL, aby złożyć dynamiczny ciąg SQL z wartościami parametrów zakodowanymi na stałe w ciągu danych. Następnie możesz przechowywać ten ciąg w zmiennej pakietowej i użyć go do ustawienia właściwości SqlCommand komponentu źródłowego. To podejście odnosi sukces, ponieważ komponent źródłowy nie wymaga już informacji o parametrach.

  Note

  Skrypt prekompilowany generuje mniej narzutu niż zadanie Execute SQL.

• Użyj parametryzowanego opakowania. Alternatywnie możesz stworzyć parametryzowaną procedurę przechowywaną jako wrapper, który wywołuje parametryzowaną funkcję tabelową. To podejście odnosi sukces, ponieważ komponent źródłowy może skutecznie wyprowadzić informacje parametrów dla procedury przechowywanej.

Ten temat wykorzystuje pierwszą opcję projektowania i składa parametryzowane zapytanie jako ciąg znaków.

Przygotowanie zapytania

Zanim będziesz mógł połączyć wartości parametrów wejściowych w jeden ciąg zapytania, musisz ustawić zmienne pakietowe, których potrzebuje zapytanie.

Aby skonfigurować zmienne pakietu

• W SQL Server Data Tools (SSDT), w oknie Zmienne, utwórz zmienną o typie danych string do przechowywania ciągu zapytania zwróconego przez zadanie Execute SQL Task.

  Ten przykład używa nazwy zmiennej, SqlDataQuery.

Po utworzeniu zmiennej pakietowej możesz użyć zadania skryptowego lub zadania Execute SQL, aby łączyć wartości parametrów wejściowych. Następujące dwie procedury opisują, jak skonfigurować te komponenty.

Użycie zadania skryptowego do łączenia ciągu zapytania

1. Na zakładce Control Flow dodaj zadanie skryptowe do pakietu po kontenerze For Loop i połącz kontener For Loop z zadaniem.

   Note

   Procedura ta zakłada, że pakiet wykonuje inkrementalne obciążenie z jednej tabeli. Jeśli pakiet ładuje się z wielu tabel i ma pakiet nadrzędny z wieloma pakietami potomnymi, to zadanie zostanie dodane jako pierwszy komponent do każdego pakietu potomnego. Więcej informacji można znaleźć w artykule Wykonaj inkrementalne obciążenie wieloma tabelami.

2. W Edytorze Zadań Skryptów, na stronie Skrypt , wybierz następujące opcje:

   1. Dla ReadOnlyVariables wybierz User::D ataReady, User::ExtractStartTime oraz User::ExtractEndTime z the.

   2. Dla ReadWriteVariables wybierz User::SqlDataQuery z listy.

3. W Edytorze zadań skryptów, na stronie Skrypt , kliknij Edytuj skrypt , aby otworzyć środowisko tworzenia skryptów.

4. W procedurze głównej wpisz jeden z następujących segmentów kodu:

   • Jeśli programujesz w C#, wpisz następujące linie kodu:

     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;  
     

     - lub -

   • Jeśli programujesz w Visual Basic, wpisz następujące linie kodu:

     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. Zostaw domyślną linię kodu, która zwraca DtsExecResult.Success z wykonania skryptu.

6. Zamknij środowisko tworzenia skryptów i Edytor zadań skryptów.

Aby użyć zadania Execute SQL do połączenia ciągu zapytania

1. Na zakładce Control Flow dodaj zadanie Execute SQL do pakietu po kontenerze For Loop i połącz kontener For Loop z tym zadaniem.

   Note

   Procedura ta zakłada, że pakiet wykonuje inkrementalne obciążenie z jednej tabeli. Jeśli pakiet ładuje się z wielu tabel i ma pakiet nadrzędny z wieloma pakietami potomnymi, to zadanie zostanie dodane jako pierwszy komponent do każdego pakietu potomnego. Więcej informacji można znaleźć w sekcji Wykonywanie przyrostowego ładowania wielu tabel.

2. W Edytorze zadań Execute SQL, na stronie Ogólnej , wybierz następujące opcje:

   1. Dla ResultSet wybierz pojedynczy wiersz.

   2. Skonfiguruj prawidłowe połączenie z bazą danych źródłową.

   3. W polu SQLSourceType wybierz opcję Wprowadzanie bezpośrednie.

   4. Aby uzyskać SQLStatement, wpisz następujące polecenie SQL:

      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  
      
      

      Note

      Klauzula else w tym przykładzie generuje zapytanie o początkowy ładunek zmian, przekazując wartość null dla daty i godziny rozpoczęcia. Ten przykład nie uwzględnia sytuacji, w której zmiany wprowadzone przed włączeniem funkcji przechwytywania zmian danych również muszą zostać załadowane do hurtowni danych.

3. Na stronie Mapowanie parametrów edytora Execute SQL Task Editor wykonaj następujące mapowanie:

   1. Odwzoruj zmienną DataReady na parametr 0.

   2. Odwzoruj zmienną ExtractStartTime na parametr 1.

   3. Odwzoruj zmienną ExtractEndTime na parametr 2.

4. Na stronie Result Set w edytorze zadań SQL Execute przypisz nazwę wyniku do zmiennej SqlDataQuery.

   Nazwa Result to nazwa pojedynczej kolumny, która jest zwracana, SqlDataQuery.

Poprzednie procedury konfigurują zadanie, które przygotowuje ciąg zapytania z na stałe zakodowanymi wartościami ciągu dla parametrów wejściowych. Poniższy kod jest przykładem takiego ciągu zapytania:

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

Dodanie zadania Przepływ danych

Ostatnim krokiem w projektowaniu przepływu sterowania dla pakietu jest dodanie zadania Przepływ danych.

Aby dodać zadanie Przepływ danych i ukończyć przepływ sterowania

• Na karcie Control Flow dodaj zadanie przepływu danych i połącz je z zadaniem, które łączy ciąg zapytania.

Treści powiązane

• Stwórz funkcję do pobrania danych zmiany