SQL Server není jen platforma na ukládání a práci s daty nebo databázemi. Je to balík služeb a kromě database enginu obsahuje také např. featury:
- Reporting Services (SSRS) – tvorba a úložiště reportů vč report serveru,
- Analysis Services (SSAS) – OLAP kostky
- Master data Services (MDS) – správa důležitých dat – např různé číselníky
- Data Quality Services (DQS) – Obsahuje nástroje pro kontrolu a opravu nekonzistentních dat před jejich uložením
- Integration Services (SSIS) – Nástroj, který umožňuje vytváření datovým pump – přelévání dat mezi systémy/platformami pomocí SQL Server Data Tools. Tyto datové pumpy se dají uložit jako balíčky a nahrát na SQL Server kde se mohou automaticky (třeba přes SQL Server Agenta) opakovaně pouštět.
Tento článek se věnuje naposledy zmíněné službě SSIS a budeme řešit problém, jak připravit automatizovaný proces, který bude každý den stahovat data z Excelu do SQL Server datového skladu.
Connection Manager v Integration Services (SSIS)?
Abychom dostali data z bodu A (např. databáze na nějakém serveru nebo Excel soubor) do bodu B (cílová SQL databáze) potřebujeme nejprve vytvořit connection na oba systémy – to proto, aby si mezi sebou následně mohli vyměnit data.
Oblast connection manageru, ve které je uveden seznam všech definovaných připojení na různé zdroje nebo cílové destinace, je uveden na obrázku níže. Protože se jedná o nově založený projekt a SSIS balíček, žádné připojení není zatím definováno.
Po kliknutí pravým tlačítkem do oblasti connection manageru je potřeba nejprve vybrat, na jaký typ dat se chceme připojovat.
Prvních 5 položek nabídky obsahuje nejpoužívanější typy připojení a volba New Connection zobrazí všechny možné typy připojení.
Typicky se budete chtít nejčastěji připojovat k nějaké databázi SQL Serveru (nebo jiné platformě) – OLE DB Connection nebo nějakému textovému souboru obsahující data oddělená oddělovačem – File connection. Další možnosti jsou samozřejmě připojení k Excelu – Excel connection manager a jiné (FTP, ODBC, SMTP, HTTP, WMI connection managers).
Příklad – Vytvoření connection managera na Excel soubor a pravidelné stahování dat do SQL Server databáze
Mějte Excel soubor, který má cestu C:\Zdroj_Dat\Zdrojova_data.xls. Tento soubor obsahuje nějaká data, která chceme každý den stahovat do SQL tabulky, protože zdrojový excel se každý den několikrát aktualizuje. Před stažením dat tabulky vždy vyčístíme pomocí SQL příkazu TRUNCATE TABLE. Excel vypadá takto:
1) Nejprve si založíme na SQL Serveru tabulku, např takto
CREATE TABLE Temp.dbo.Priklad (
Datum DATE NULL,
Hodnota INT NULL
);
2.a) Z nabídky nad oblastí connection manaers vybereme “New connection” a následně “EXCEL”
2.b) Následně nastavíme cestu na zdrojový Excel a v seznamu připojení se objeví nový typ připojení
3) Připojení na zdrojový soubor je připraveno a stejným způsobem nastavíme připojení na cílovou SQL Server databázi “Temp”. To provedeme podobným způsobem s tím rozdílem, že jako typ připojení vybereme OLE DB connection. Dále nastavíme server a databázi.
4) Nyní je čas nadesignovat jednoduchou datovou pumpu. SSIS je velmi modulární a skládá se z jednotlivých kroků. Jelikož chceme cílovou tabulku před každým loadem dat z Excelu vyčistit, tak prvník krokem bude Execute SQL Task. Z SSIS toolboxu vybereme danou úlohu a přetáhneme do prostoru Control flow.
Po přetažení se objeví červený křížek. To znamená, že něco není v pořádku. Task zatím nemá totiž nastaveno propojení s connection managerem. Nastavení provedeme editací Tasku a vyplněním Connection manageru a příkazu, který se má nad databází vykonat. V našem případě TRUNCATE TABLE Temp.dbo.Priklad.
5) Nakonec ještě musíme nastavit samotnou datovou pumpu. K tomu slouží Data flow task. Opět jej přetáhneme do prostoru control flow
6) Poté na data flow task poklikáme a nastavíme jej jako na obrázku níže. Z SSIS toolboxu vybereme Excel source a jako destination vybereme OLE DB Destination. Komponenty nastavíme na connection managery a propojíme šipkou. Je hotovo
7) Když celý balíček spustíme, tak bychom měli vidět toto a…
…pokud se podíváme do cílové tabulky, měly by tam být data z excelu:
Datovou pumpu je možné spustit vícekrát, protože na začátku se data vždy vyčistí.
8) Aby mohl být tento připravený SSIS balíček pouštěn automaticky, tak je možné využít dvě možností:
- Buď balíček uložíte a .dtsx soubor uložíte někam na server
- Nebo celý balíček nahrajete příkazem deploy na server do katalogu
Následně si v SQL Server Agentovi vytvořítě job a jako “Step” mu nastavíte Step Type = SQL Server Integration Services package s cestou na dtsx soubor. Jako poslední krok jobu nastavíte jak často a v jakou hodinu se má pouštět a pak se již nemusíte o nic starat.
