Ujednolicony SSIS

https://xpil.eu/Q6kTo 📋

Na horyzoncie już SQL Server 2016, a ja dopiero się oswajam z wersją 2012. No nic. Lepiej tak, niż wcale.

SSIS, technologia Microsoftu o chyba o najczęściej zmienianej nazwie, również ewoluuje. Dziś napiszę odrobinę o tym, jak Microsoft ujednolicił konfigurowanie paczek SSIS w wersji 2012.

Małe wyjaśnienie: według polskiej nomenklatury, w przypadku SSIS mówi się o pakietach, a nie o paczkach. Ale ja się jakoś przyzwyczaiłem do używania w tym kontekście słowa "paczka" i będę pisał "paczka". Jeżeli się to komuś nie podoba - trudno...

Otóż przenieśli paczki SSIS do osobnej bazy danych. Dotychczas pakiety były przechowywane bądź w plikach dtsx, bądź też w systemowej bazie msdb, w tabeli sysssispackages. Tymczasem od wersji 2012 pojawiła się osobna, opcjonalna baza o nazwie SSISDB, oferująca o wiele bardziej rozbudowany i funkcjonalny model przechowywania projektów SSIS.

Tak, całych projektów!

Można w tej bazie tworzyć wirtualne foldery, które pomogą nam uporządkować nasze projekty SSIS. W ramach każdego takiego folderu są sobie projekty, a każdy projekt zawiera rozmaite obiekty - głównie paczki ("packages") SSIS, ale nie tylko.

No i teraz najfajniejsze: jeżeli skonfigurujemy sobie w projekcie kilka środowisk docelowych, możemy ustawić bardzo szczegółowe parametry dla każdego środowiska. Najprostszy przykład: definiujemy sobie środowiska DEV, TEST i PROD i dla każdego z nich ustalamy sobie inne parametry połączeń do baz danych. Albo do folderów z plikami źródłowymi (albo docelowymi). Albo loginy i hasła. I tak dalej, i tak dalej.

Kolejne udogodnienie to możliwość definiowania niektórych obiektów i parametrów na poziomie projektu - na przykład możemy mieć JEDNO połączenie do bazy (obiekt typu Connection) w projekcie i używać tego jednego połączenia w każdej paczce. Dzięki temu jeżeli zmieni się jakiś parametr połączenia (na przykład hasło albo adres serwera itd), wystarczy zmienić to w JEDNYM miejscu, a nie w iluś-tam.

Jak już zbudujemy wszystkie potrzebne do projektu paczki, klikamy prawym myszem w drzewku projektu na jego nazwę, wybieramy Deploy, wskazujemy docelowy serwer i folder wirtualny - i cały nasz projekt (w moim przypadku największy taki projekt miał coś ponad sześćset paczek SSIS) ląduje w bazie SSISDB na serwerze docelowym, skonfigurowany konkretnie pod zadane środowisko (dev, test, prod czy cokolwiek innego).

Bazę SSISDB możemy przechowywać na jednym serwerze (i tam, w osobnych wirtualnych folderach, trzymać projekty dla poszczególnych środowisk), albo możemy mieć osobną instancję bazy SSISDB na każdym z serwerów dev, test itd. - w tę czy we w tę, zawsze mamy konkretnie zdefiniowane projekty w konkretnie zdefiniowanych lokalizacjach, sparametryzowane pod kątem środowisk, w których będą one działać.. Bardzo wygodne. I bardzo logicznie uporządkowane.

Pozostaje jeszcze pytanie: w jaki sposób uruchamiać paczki przechowywane w bazie SSISDB?

Tu sprawa się nieco komplikuje, albo - w zależności od punktu widzenia - upraszcza.

Oczywiście można się doklikać do paczki bezpośrednio z poziomu SSMS, dźgnąć ją prawym myszem i wybrać "Execute". Ale co, jeżeli chcemy uruchomienie paczki zautomatyzować?

Poprzednio mieliśmy do dyspozycji zewnętrzną utilkę dtexec.exe, za pomocą której uruchamiało się paczki SSIS (zarówno te z plików jak też te z bazy msdb). Natomiast w przypadku projektów SSIS składowanych w bazie SSISDB możemy "zapuszczać" paczki za pomocą "czystego" SQL-a. Nie musimy opuszczać środowiska SQL, żeby uruchomić paczkę, a przedtem możemy ją dodatkowo sparametryzować, skonfigurować i tak dalej.

Aby uruchomić paczkę SSIS z bazy SSISDB, musimy najpierw utworzyć kontekst uruchomieniowy:

Declare @execution_id bigint
EXEC SSISDB.catalog.create_execution @package_name=N'NazwaPaczki.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'NazwaFolderuZProjektem', @project_name=N'NazwaProjektuSSIS', @use32bitruntime=False, @reference_id=Null

Powyższy kod utworzy nowy kontekst uruchomieniowy, przypisze mu unikalną wartość numeryczną i zwróci ją do zmiennej wyjściowej @execution_id. Następnie możemy opcjonalnie skonfigurować dodatkowe parametry (za pomocą procedury set_execution_parameter_value - proszę sobie poguglać po szczegóły), wreszcie samo uruchomienie paczki realizuje się za pomocą procedury start_execution, o tak:

EXEC SSISDB.catalog.start_execution @execution_id

Paczka zaczyna działać, zapisując szczegółowy log z wykonania do osobnych tabel w bazie SSISDB (można sobie w ten sposób monitorować wykonywanie paczki bez konieczności opuszczania środowiska SQL).

Jak widać, zmiany w porównaniu do poprzedniej wersji (tj. 2008 R2) są dość znaczne, ale zdecydowanie idzie ku lepszemu.

Moje ulubione ulepszenia to:

1. Obiekty na poziomie projektu
2. Parametryzacja paczek per środowisko (to akurat nie jest nowością, ale nigdy przedtem tego nie używałem, więc DLA MNIE to nowość:) )
3. Baza SSISDB i "ekosystem" procedur i tabel, pozwalający łatwo parametryzować, uruchamiać i monitorować poszczególne procesy.

Ot, co.

https://xpil.eu/Q6kTo 📋