Przewodnik: publikowanie pakietu usług SSIS jako widoku SQL

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

Ten przewodnik zawiera szczegółowe kroki publikowania pakietu SSIS jako widoku SQL w bazie SQL Server.

Wymagania wstępne

Musisz mieć zainstalowane następujące oprogramowanie na swoim komputerze, aby przeprowadzić ten przegląd.

1. SQL Server z programem SQL Server Integration Services.

2. Narzędzia danych programu SQL Server.

Krok 1: Zbuduj i wdroż SSIS Project do katalogu SSIS

W tym etapie tworzysz pakiet SSIS, który wyodrębnia dane ze źródła obsługiwanego przez SSIS – w tym przykładzie używamy bazy danych SQL Server – i generuje dane za pomocą komponentu Data Streaming Destination. Następnie budujesz i wdrażasz projekt SSIS do katalogu SSIS.

1. Uruchom SQL Server Data Tools. W menu Start wskaż Wszystkie programy, Microsoft SQL Server i kliknij SQL Server Data Tools.

2. Stwórz nowy projekt Integration Services.

   1. Kliknij Plik na pasku menu, wskaż Nowe i kliknij Project.

   2. Rozwiń Business Intelligence w lewym panelu i kliknij Usługi integracyjne w widoku drzewa.

   3. Wybierz Integration Services Project, jeśli nie jest jeszcze wybrany.

   4. Określ nazwę projektuSSISPackagePublishing.

   5. Określ lokalizację projektu.

   6. Kliknij OK, aby zamknąć okno dialogowe New Project.

3. Przeciągnij komponent Przepływ danych z SSIS Toolbox na powierzchnię projektową zakładki Control Flow.

4. Kliknij dwukrotnie komponent Przepływ danych w Control Flow, aby otworzyć Przepływ danych Designer.

5. Przeciągnij komponent źródłowy z toolboxa do Przepływ danych Designera i skonfiguruj go do wyodrębniania danych ze źródła danych.

   1. Na potrzeby przejścia stwórz bazę testową: TestDB z tabelą: Employee. Stwórz tabelę z trzema kolumnami: ID,Imię i Nazwisko.

   2. Ustaw ID jako klucz główny.

   3. Wstaw dwa rekordy z następującymi danymi.

      identyfikator	FIRSTNAME	LASTNAME
      1	John	Doe
      2	Jane	Doe

   4. Przeciągnij komponent OLE DB Source z SSIS Toolbox do Przepływ danych Designer.

   5. Konfiguruj komponent do wyodrębniania danych z tabeli Pracownik w bazie TestDB . Wybierz (local).TestDB w polu menedżer połączeń OLE DB, Tabela lub widok w polu Tryb dostępu do danych oraz [dbo].[Employee] w polu Nazwa tabeli lub widoku.

      

6. Teraz przeciągnij Miejsce docelowe strumienia danych z zestawu narzędzi do przepływu danych. Ten komponent powinieneś znaleźć w sekcji Wspólne w skrzynce narzędziowej.

7. Połącz komponent OLE DB Source w przepływie danych z komponentem Data Streaming Destination.

8. Zbuduj i wdroż projekt SSIS w katalogu SSIS.

   1. Kliknij Project na pasku menu i wybierz Deploy.

   2. Postępuj zgodnie z instrukcjami w kreatorze, aby wdrożyć projekt do katalogu SSIS na lokalnym serwerze bazy danych. Poniższy przykład używa Power BI jako nazwy folderu oraz SSISPackagePublishing jako nazwy projektu w katalogu SSIS.

Krok 2: Użyj kreatora publikowania kanału danych SSIS, aby opublikować pakiet SSIS jako widok SQL

W tym kroku użyjesz Kreatora publikowania źródła danych SQL Server Integration Services (SSIS), aby opublikować pakiet SSIS w postaci widoku w bazie danych SQL Server. Dane wyjściowe pakietu można wykorzystać poprzez zapytanie w tym widoku.

Kreator publikacji danych SSIS tworzy serwer powiązany za pomocą dostawcy OLE DB dla SSIS (SSISOLEDB), a następnie tworzy widok SQL składający się z zapytania na serwerze połączonym. To zapytanie zawiera nazwę folderu, nazwę projektu i nazwę pakietu w katalogu usług SSIS.

W czasie wykonywania widok wysyła zapytanie za pośrednictwem utworzonego przez Ciebie serwera połączonego do dostawcy OLE DB dla usługi SSIS. Dostawca OLE DB dla usług SSIS wykonuje pakiet określony w zapytaniu i zwraca tabelaryczny zestaw wyników w odpowiedzi na to zapytanie.

1. Uruchom Kreator publikowania strumienia danych SSIS, uruchamiając plik ISDataFeedPublishingWizard.exe z folderu C:\Program Files\Microsoft SQL Server\130\DTS\Binn lub klikając pozycję Microsoft SQL Server 2016\Kreator publikowania strumienia danych programu SQL Server 2016 w menu Start\Wszystkie programy.

2. Kliknij Dalej na stronie Wprowadzające .

   

3. Na stronie Ustawienia pakietu wykonaj następujące zadania:

   1. Wpisz nazwę instancji SQL Server, która zawiera katalog SSIS, lub kliknij Przeglądaj, aby wybrać serwer.

      

   2. Kliknij Przeglądaj obok pola Ścieżka, przejrzyj katalog SSIS, wybierz pakiet SSIS, który chcesz opublikować (na przykład: SSISDB-SSISPackagePublishing-Package.dtsx>>) i kliknij OK.

      

   3. Korzystając z kart Parametry pakietu, Parametry projektu i Menedżery połączeń u dołu strony, wprowadź wartości dla dowolnych parametrów pakietu, parametrów projektu lub ustawień menedżera połączeń dla pakietu. Możesz także wskazać odniesienie środowiskowe do wykonania pakietu i powiązać parametry projektu/pakietu ze zmiennymi środowiskowymi.

      Zalecamy powiązanie wrażliwych parametrów ze zmiennymi środowiskowymi. Ma to zapewnić, że wartość wrażliwego parametru nie jest przechowywana w formacie tekstu jawnego w widoku SQL utworzonym przez kreatora.

   4. Kliknij Dalej , aby zmienić stronę Opublikuj Ustawienia .

4. Na stronie Ustawienia publikowania wykonaj następujące zadania:

   1. Wybierz bazę danych , aby utworzyć widok.

      

   2. Wpisz nazwęwidoku. Możesz także wybrać istniejący widok z listy rozwijanej.

   3. W Ustawieniach określ nazwępołączonego serwera , który ma być powiązany z widokiem. Jeśli serwer powiązany jeszcze nie istnieje, kreator utworzy serwer powiązany przed utworzeniem widoku. Możesz też tutaj ustawić wartości User32BitRuntime i Timeout.

   4. Kliknij przycisk Zaawansowane . Powinieneś zobaczyć okno dialogowe Zaawansowane Ustawienia .

   5. W oknie dialogowym Zaawansowane ustawienia zrób następujące czynności:

      1. Określ schemat bazy danych, w którym chcesz utworzyć widok (pole schematu).

      2. Określ, czy dane powinny być szyfrowane przed wysłaniem ich przez sieć (pole Encrypt). Zobacz temat Używanie szyfrowania bez walidacji , aby uzyskać więcej szczegółów na temat tego ustawienia oraz ustawienia TrustServerCertificate.

      3. Określ, czy certyfikat serwera podpisany samodzielnie można użyć, gdy ustawienie szyfrowania jest włączone (pole TrustServerCertificate ).

      4. Kliknij przycisk OK , aby zamknąć okno dialogowe Ustawienia zaawansowane .

   6. Kliknij Dalej , aby przejść na stronę Walidacja .

5. Na stronie Walidacja przejrzyj wyniki z weryfikacji wartości dla wszystkich ustawień. W poniższym przykładzie widzisz ostrzeżenie o istnieniu serwera połączonego, ponieważ serwer powiązany nie istnieje na wybranej instancji SQL Server. Jeśli zobaczysz Błąd dla Wyniku, najedź kursorem na Błąd, a zobaczysz szczegóły dotyczące błędu. Na przykład, jeśli nie włączyłeś opcji Zezwalaj na proces dla dostawcy SSISOLEDB, pojawi się błąd w akcji Konfiguracja serwera połączonego.

   

6. Aby zapisać ten raport jako plik XML, kliknij Zapisz raport.

7. Kliknij Dalej na stronie Walidacja , aby przejść na stronę Podsumowanie .

8. Przejrzyj swój wybór na stronie Podsumowanie i kliknij Opublikuj , aby rozpocząć proces publikacji, który utworzy powiązany serwer, jeśli nie istnieje on na serwerze, a następnie utworzy widok na tym powiązanym serwerze.

   

   Dane wyjściowe pakietu można teraz wyszukiwać, wykonując następujące polecenie SQL na bazie TestDB: SELECT * FROM [SSISPackageView].

9. Aby zapisać ten raport jako plik XML, kliknij Zapisz raport.

10. Przejrzyj wyniki procesu publikowania i kliknij Zakończ, aby zamknąć kreator.

    Note

    Następujące typy danych nie są obsługiwane: text, ntext, image, nvarchar(max), varchar(max) oraz varbinary(max).

Krok 3: Przetestuj widok SQL

W tym trybie uruchomisz widok SQL utworzony przez kreator publikowania danych SSIS.

1. Uruchom program SQL Server Management Studio.

2. Rozwiń <>, Bazy danych, <bazę danych wybraną w kreatorze> oraz Widoki.

3. Kliknij prawym przyciskiem myszy <widok utworzony przez kreatora> utworzony przez kreatora i kliknij Wybierz 1000 pierwszych wierszy.

4. Potwierdź, że widzisz wyniki pakietu SSIS.

Krok 4: Zweryfikowaj wykonanie pakietu SSIS

W tym kroku zweryfikowasz, czy pakiet SSIS został wykonany.

1. W programie SQL Server Management Studio rozwiń węzeł Integration Services Catalogs, rozwiń węzeł SSISDB, rozwiń folder, w którym znajduje się Twój projekt SSIS, rozwiń węzeł Projects, rozwiń węzeł swojego projektu, a następnie rozwiń węzeł Packages.

2. Kliknij prawym przyciskiem myszy na pakiet SSIS, następnie wskazać na Raporty, następnie na Standardowe Raporty, a następnie wybierz Wszystkie realizacje.

3. Powinieneś zobaczyć wykonanie pakietu SSIS w raporcie.

   Note

   Na komputerze z Windows Vista Service Pack 2 możesz zobaczyć dwa uruchomienia pakietów SSIS w raporcie – jedno udane i jedno nieudane. Zignoruj ten nieudany wynik, ponieważ wynika to ze znanego problemu w tym wydaniu.

Więcej informacji

Kreator publikacji strumienia danych wykonuje następujące ważne kroki:

1. Tworzy serwer powiązany i konfiguruje go do korzystania z dostawcy OLE DB Provider dla SSIS.

2. Tworzy widok SQL w określonej bazie danych, który pobiera z serwera połączonego informacje katalogowe dla wybranego pakietu.

Ta sekcja zawiera procedury tworzenia połączonego serwera oraz widoku SQL bez użycia Kreatora publikacji w Data Feed. Zawiera także dodatkowe informacje o korzystaniu z funkcji OPENQUERY z dostawcą OLE DB dla SSIS.

Utworzenie serwera połączonego za pomocą dostawcy OLE DB dla SSIS

Utworzenie serwera powiązanego za pomocą dostawcy OLE DB Provider for SSIS (SSISOLEDB), uruchamiając następujące zapytanie w SQL Server Management Studio.

  
USE [master]  
GO  
  
EXEC sp_addlinkedserver  
@server = N'SSISFeedServer',  
@srvproduct = N'Microsoft',  
@provider = N'SSISOLEDB',  
@datasrc = N'.'  
GO  
  

Tworzenie widoku przy użyciu serwera połączonego i informacji z katalogu SSIS

W tym kroku utworzysz widok SQL, który wykona zapytanie na połączonym serwerze utworzonym w poprzedniej sekcji. Zapytanie będzie zawierać nazwę folderu, nazwę projektu oraz nazwę pakietu w katalogu SSIS.

W czasie działania, gdy widok jest wykonywany, powiązane zapytanie serwera zdefiniowane w widoku uruchamia pakiet SSIS określony w zapytaniu i otrzymuje wyjście pakietu jako tabelarzowy zestaw wyników.

1. Przed utworzeniem widoku wpisz i uruchom następujące zapytanie w nowym oknie zapytania. OPENQUERY to funkcja wierszowa obsługiwana przez SQL Server. Wykonuje określone zapytanie przejściowe na określonym serwerze połączonym, korzystając z dostawcy OLE DB powiązanego z tym serwerem. W klauzuli FROM zapytania można odwoływać się do pliku OPENQUERY tak, jakby była to nazwa tabeli. Więcej informacji można znaleźć w dokumentacji OPENQUERY dotyczącej biblioteki MSDN .

   SELECT * FROM OPENQUERY(SSISFeedServer,N'Folder=Eldorado;Project=SSISPackagePublishing;Package=Package.dtsx')   
   GO  
   

   Ważna

   Zaktualizuj nazwę folderu, projekt i nazwę pakietu, jeśli zajdzie taka potrzeba. Jeśli funkcja OPENQUERY zawiodła, w SQL Server Management Studio rozwiń obiekty serwera, rozwiń serwery połączone, rozwiń dostawców i kliknij dwukrotnie dostawcę SSISOLEDB, a także upewnij się, że opcja Zezwalaj na proces jest włączona.

2. Utworzenie widoku w bazie danych TestDB na potrzeby tego poradnika) wykonując następujące zapytanie.

   
   USE [TestDB]   
   GO   
   
   CREATE VIEW SSISPackageView AS   
   SELECT * FROM OPENQUERY(SSISFeedServer, 'Folder=Eldorado;Project=SSISPackagePublishing;Package=Package.dtsx')   
   GO  
   
   

3. Przetestuj widok, uruchamiając następujące zapytanie.

   SELECT * FROM SSISPackageView  
   

Funkcja OPENQUERY

Składnia funkcji OPENQUERY wygląda następująco:

SELECT * FROM OPENQUERY(<LinkedServer Name>, N'Folder=<Folder Name from SSIS Catalog>; Project=<SSIS Project Name>; Package=<SSIS Package Name>; Use32BitRuntime=[True | False];Parameters="<parameter_name_1>=<value1>; parameter_name_2=<value2>";Timeout=<Number of Seconds>;')  

Parametry folderów, Project i Package są obowiązkowe. Use32BitRuntime, Timeout i Parameters są opcjonalne.

Wartość Use32BitRuntime może wynosić 0, 1, prawdziwe lub fałszywe. Wskazuje, czy pakiet powinien działać z 32-bitowym środowiskiem wykonawczym (1 lub true), gdy platforma SQL Server jest 64-bitowa.

Timeout wskazuje liczbę sekund, na ile dostawca OLE DB dla SSIS może czekać, zanim nowe dane dotrą z pakietu SSIS. Domyślnie timeout wynosi 60 sekund. Możesz określić wartość całkowitą dla limitu czasu między 20 a 32000.

Parametry zawierają wartości zarówno parametrów pakietu, jak i projektu. Reguły dotyczące parametrów są takie same jak w DTExec.

Poniższa lista określa dozwolone znaki specjalne w klauzuli zapytania:

• Pojedynczy cudzysłów (') – Jest to obsługiwane przez standardowy OPENQUERY. Jeśli chcesz użyć pojedynczego cudzysłowu w klauzuli zapytania, użyj dwóch pojedynczych cudzysłowów ('').

• Double-Quote (") - Część zapytania dotycząca parametrów jest otoczona podwójnymi cudzysłowami. Jeśli wartość parametru zawiera podwójny cudzysłów, użyj znaku escape. Na przykład: ".

• Nawiasy kwadratowe po lewej i prawej stronie ([ i ]) – Te znaki służą do oznaczania odstępów prowadzących/tylnych. Na przykład „[ some spaces ]” oznacza ciąg znaków „ some spaces ” z jedną spacją na początku i jedną na końcu. Jeśli te same znaki są używane w klauzuli zapytania, muszą zostać poprzedzone znakiem ucieczki. Na przykład: \[ i \].

• Ukośnik do przodu (\) - Każde \ użyte w klauzuli zapytania musi używać znaku escape. Na przykład \\ jest interpretowane jako \ w klauzuli zapytania.

Treści powiązane

• Miejsce docelowe przesyłania strumieniowego danych