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.

Oblast connection manageru v SSIS

Po kliknutí pravým tlačítkem do oblasti connection manageru je potřeba nejprve vybrat, na jaký typ dat se chceme připojovat.

C:\Users\janz\Desktop\založení connection manageru.png

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:

Connection manager - příklad zdrojovych dat

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”

Excel connection manager

2.b) Následně nastavíme cestu na zdrojový Excel a v seznamu připojení se objeví nový typ připojení

Excel connection manager a cesta na soubor

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.

OLE DB connection manager

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.

Execute SQL Task SSIS

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

Data flow task SSIS

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

Nastavení data flow tasku

7) Když celý balíček spustíme, tak bychom měli vidět toto a…

SSIS Datová pumpa proběhla úspěšně

…pokud se podíváme do cílové tabulky, měly by tam být data z excelu:

Kontrola tabulky po exekuci datové pumpy

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.

5/5 - (1 vote)

Ing. Jan Zedníček - Data & Finance

Jmenuji se Honza Zedníček a působím jako freelancer. Tuto práci dělám pro různé firmy již přes 10 let. Před tím jsem dlouhou dobu pracoval také jako BI developer, finanční controller, analytik a krátce jako finanční manažer. Vše pro společnosti z oblasti IT, bankovnictví a výroby. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti a názory se snažím už několik let zapisovat na tento web, aby sloužily i někomu dalšímu - zejména studentům a zájemcům o informace z oblastí Business intelligence, korporátních financí a reportingu.

=> Přihlašte se do naší Excel facebook skupiny (1.4k+ členů), kde si pomáháme Excel CZ/SK diskuse »
=> Pokud vám článek pomohl, sdílejte ho nebo mě uveďte na vašich webových stránkách jako užitečný zdroj informací.

Leave a Reply

Your email address will not be published. Required fields are marked *