SSIS Connection Manager + Příklad Jednoduché Datové Pumpy

Poslední aktualizace:

Na úvod zmíním důležitou věc, kterou mnoho lidí neví. 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 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 databáze.

Co je 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.

SSIS Connection Manager + Příklad Jednoduché Datové Pumpy
5 (100%) 1 vote[s]

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Category: SSIS - Integration Services

About Ing. Jan Zedníček - BI Developer, Finance controller

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.