SSIS: OLEDB vs ADO.NET i parametry

https://xpil.eu/22e

Jako że na co dzień pracuję w szeroko pojętej branży Business Intelligence, jednym z moich głównych zajęć w większości firm, w których miałem przyjemność (bądź też, w jednym czy dwóch wstydliwych przypadkach, nieprzyjemność) pracować jest praca z hurtowniami danych.

Z hurtowniami danych jest trochę jak z pecetami: wieszczono im rychłą smierć już ładnych paręnaście lat temu (najpierw laptopy/ultrabooki, potem tablety, smartfony, blabla...), a tymczasem miewają się one do dziś całkiem nieźle i nic nie zwiastuje ich zniknięcia w ciągu najbliższych paru lat.

A w hurtowni danych, jak wiadomo, jednym z ważnych elementów jest ETL. Czyli w sporym uproszczeniu mówiąc przewalanie danych z punktu A do punktu B, z ewentualną ich obróbką po drodze (w punkcie Ą, w końcu Polacy nie gęsi).

Do ETL-a narzędzi jest dużo. Jedne lepsze, drugie gorsze, trzecie całkiem do bani. Microsoftowa technologia SSIS (kiedyś: DTS) przez ostatnich parę lat wyewoluowała z brzydkiego i nielubianego kaczątka do całkiem sprawnej i eleganckiej łabędzicy. Oczywiście do niektórych zastosowań SSIS nadaje się lepiej, do innych gorzej - ale w ogólnym rozrachunku z wersji na wersję jest coraz lepiej.

Niestety, jak to z Microsoftem bywa, ciąży na nich widmo wstecznej kompatybilności. I tak, jak na Windows 10 można wciąż uruchamiać Sapera z Windowsa 3.0, tak w SSIS ciągle można używać technologii bazodanowych, które się współcześnie już nie sprawdzają, ale które tam ciągle są, bo a nuż ktoś ich jednak będzie kiedyś potrzebował. Coś jak ze strychem stryja Zenona: nie wyrzucamy tej starej nogi od nieistniejącej już kanapy, bo a nuż.

Jedną z takich technologii jest OLEDB, czyli taki standard dostępu do danych oraz ich wymiany między systemami.

Nie zamierzam dziś opowiadać o tym, czym OLEDB jest (albo nie jest), bo zrobiłoby się nudno. Opowiem tylko o jednej, w sumie dość (dla mnie!) ciekawej przygodzie z tą technologią, która mnie spotkała jakiś czas temu nazad.

Otóż OLEDB "nie rozumie" typu danych VARCHAR(MAX) (ani NVARCHAR(MAX)). No nie rozumie i koniec. Jeżeli z jakiegoś źródełka przybywają takie dane, zostaną one potraktowane jako VARCHAR(8000) (lub NVARCHAR(4000)), o ile tylko ilość danych w jednym polu nie przekroczy owej magicznej granicy ośmiu kilobajtów (czyli 8000 znaków VARCHAR lub 4000 znaków NVARCHAR). Wtedy bowiem OLEDB idzie w najgłębsze maliny, natychmiast zmienia "w locie" typ danych na DBNULL, rzuca błędem i dupa blada.

Rozwiązaniem jest zmiana technologii z OLEDB na ADO.NET.

I wszystko pięknie tam bangla w tym ADO.NET, bo przecież składnia SQL się nie zmienia. Jedyne co się zmienia to bardziej "nowoczesny" sposób transferowania danych między węzłami sieci. Sposób, który "rozumie" wszystkie typy danych, jakie są dostępne na SQL Server.

Niestety, w momencie przełączenia się z OLEDB na ADO.NET tracimy jedną bardzo cenną opcję: możliwość sparametryzowania zapytania na poziomie SSIS.

Yyyyy... zaduma się zapewne lekko już ziewający Czytelnik. Yyyy?

Nie żadne yyyy tylko parametryzacja. Przecież wyraźnie mówię, c'nie?

W "starym" OLEDB możemy wpisać zapytanie SQL w takiej postaci:

SELECT a, b, c FROM jakas_tabelka WHERE jakis_numer BETWEEN ? AND ?;

Następnie wejść w zakładkę "Parametry" i nakazać pierwszemu parametrowi przyjąć wartość 7 a drugiemu 19. W efekcie do bazy zostanie wysłane zapytanie postaci:

SELECT a, b, c FROM jakas_tabelka WHERE jakis_numer BETWEEN 7 AND 19;

Oczywiście powyższy przykład jest bardzo uproszczony. W rzeczywistym scenariuszu zamiast 7 i 19 będziemy mieli odwołanie do dwóch lokalnych zmiennych SSIS, dajmy na to [User::JakasLiczba] oraz [User::JakasInnaLiczba].

Niestety, zmiana technologii z OLEDB na ADO.NET spowoduje, że SSIS nas wyśmieje. A idź pan, panie, z tymi znakami zapytania, w dupę je pan sobie wsadź.

Brzmi kusząco, ale zamiast bawić się w małego proktologa, musimy zagadnienie obejść.

Skoro nie da się bydlęcia sparametryzować...

... skorzystamy z dobrodziejstwa WYRAŻEŃ.

Wyrażenia ("Expressions") to małe, ale bardzo przydatne stwory skojarzone z prawie każdym elementem SSIS. Klikamy prawym myszem w dowolny element SSIS, wybieramy Properties i w 99 przypadkach na 100 będziemy mieli dostęp do sekcji Expressions tegoż elementu.

Każde expression jest skojarzone z konkretną właściwością elementu. Jeżeli expression dla danej właściwości jest puste, wówczas właściwość ta zachowuje się "normalnie" (a więc przyjmuje wartość nadaną jej przez programistę). Jeżeli natomiast expression jest niepuste, wówczas każdorazowo przed odczytaniem wartości najpierw następuje wyliczenie tej wartości na podstawie expression. To sprawia, że dana właściwość elementu staje się dynamiczna.

W przypadku połączenia ADO.NET spójrzmy na właściwość SqlStatementSource:

Na początek możemy tam wpisać na stałe:

"SELECT a, b, c FROM jakas_tabelka WHERE jakis_numer BETWEEN 7 AND 19;"

W ten sposób oczywiście nie zyskujemy NIC, ponieważ dokładnie to samo zapytanie moglibyśmy wpisać po prostu w polu SQLStatement w edytorze połączenia.

Ale (jak pamiętamy) mamy w naszym pakiecie SSIS dwie zmienne: [User::JakasLiczba] oraz [User::JakasInnaLiczba]. Możemy je teraz wykorzystać. W Expressions, dla właściwości SqlStatementSource wpisujemy:

"SELECT a, b, c FROM jakas_tabelka WHERE jakis_numer BETWEEN " + @[User::JakasLiczba] + " AND " + @[User::JakasInnaLiczba]

Tu właśnie dokonaliśmy niemożliwego, czyli sparametryzowaliśmy wyrażenie SQL przy użyciu technologii, która na tę parametryzację nie pozwala.

De facto cała praca polegająca na przygotowaniu zapytania spada teraz na klienta (czyli silnik wykonawczy SSIS). W przypadku OLEDB bowiem było tak, że wysyłaliśmy do bazy danych zapytanie ze znakami zapytania (oznaczającymi miejsce występowania parametrów), a także wartości tych parametrów, które SQL Server już samodzielnie wstawiał we właściwe miejsca. Natomiast teraz treść zapytania SQL jest wyliczana w całości po stronie klienta, wartości parametrów trafiają gdzie trzeba, i dopiero wtedy zapytanie jest wysyłane do SQL Servera.

Fascynujące, nieprawdaż?

https://xpil.eu/22e

Leave a Comment

Komentarze mile widziane.

Jeżeli chcesz do komentarza wstawić kod, użyj składni:
[code]
tutaj wstaw swój kod
[/code]

Jeżeli zrobisz literówkę lub zmienisz zdanie, możesz edytować komentarz po jego zatwierdzeniu.