V článku o control flow tasku jsem popsal funkci Control Flow v SSIS balíčku. Pomocí control flow dokážeme řídit to, co balíček provádí. Nejpoužívanejším typem tasku je právě Data flow task, který obsahuje logiku datových toků (ETL procesů).
Rozdíl mezi Control Flow a Data flow taskem
Bohužel se tyto 2 pojmy často zaměňují. Control flow není task, ale je to řídící vrstva SSIS package. Definuje posloupnost jednolivých tasků (např. Execute SQL, Script task, Dataflow task, apod) a vztahy mezi nimi.
Data flow je pouze jedním z tasků, které je možné zaimplementovat do Control flow a nachází se v něm logika jak dostat data z bodu A do bodu B. Control flow může obsahovat 1 nebo více Data flow tasků.
Data Flow – Úvod
Jak jsem zmínil dříve, je asi nejpoužívanějším taskem. Důvodem je to, že SSIS package nejčastěji vytváříme abychom zajistili nějaký datový tok, případně transformaci dat před uložením do cílové tabulky. A k tomuto je Data Flow task určen.
Data flow zajišťuje ve spolupráci s connection managerem připojení na zdroj dat (a cílovou destinaci), dále transformace a tok dat. Na screenshotu níže je stav našeho SSIS package z předchozího článku, kdy jsme připravili control flow. Máme tam Execute SQL tasky, které zajišťují logování (na začátku a na konci) a dále data flow task. Ten ale zatím neobsahuje žádnou logiku.
Data flow task na screenshotu je uprostřed, pojmenovaný jako “ETL”. Tento task si můžeme rozkliknout a podívat se dovnitř.
Obsah tasku je zatím zcela prázdný. Všimněte si, že v SSIS Toolboxu (vlevo v pracovním prostoru) máme najednou jinou nabídku komponent.
SSIS Data Flow – Typy komponent, Source, Destination
Pojďme se porozhlédnout po nabídce SSIS Toolboxu – tedy co vlastně máme v Data flow k dispozici v defaultním nastavení Business Intelligence Studia. Toolbox lze zjednodušeně rozdělit na 2 základní skupiny:
- Připojení ke zdroji dat a cílové destinaci
- Datové Transformace
V SSIS Toolboxu se komponenty dělí na:
1 Připojení ke zdroji dat
-
- 1.1. Favorites – Najdeme zde Source a Destination Assistenta (průvodce), který nám pomůže vytvořit providera na nějaký soubor nebo databázovou tabulku
- 1.2. Other Sources – Providera připojení si můžeme zvolit, např. pro připojeni k SQL Server bychom vybrali OLE DB Source – data slouží jako zdroj ze kterého můžeme číst data
- ADO NET Source
- CDC Source
- Excel Source
- Flat File Source
- ODBC Source
- OLE DB Source
- Raw File Source
- XML Source
- 1.3. Other Destination – Providera připojení si můžeme zvolit, např. pro připojeni k SQL Server bychom vybrali OLE DB Source – data se do této destinace budou ukládat
- ADO NET Destination
- Data Mining Model Training
- DataReader Destination
- Dimension processing
- Excel Destination
- Flat File Destination
- ODBC Destination
- OLE DB Destination
- Partion Processing
- Raw File Destination
- Recordset Destination
- SQL Server Compact Destination
- SQL Server Destination
2 Datové transformace
-
- 2.1. Common – Najdeme zde nejpoužívanější nástroje pro datové transformace, např Derived Column (přidání nějakého sloupce), Data Conversion (změna definice sloupců), atp
- Aggregate component
- Balanced Data Distributor
- Conditional Split
- Data Conversion
- Data Streaming Destination
- Derived Column
- HDFS File Destination
- HDFS FIle Source
- Lookup component
- Merge component
- Merge Join component
- Multicast component
- OData Source
- OLE DB Command
- Row Count component
- Script Component
- Slowly Changing dimension
- Sort component
- Union All component
- 2.2. Other Transformations
- Audit
- Cache Transform
- CDC Splitter
- Character Map
- Copy Column
- Data Mining Query
- DQS Cleansing
- Export Column
- Fuzzy Grouping
- Fuzzy Lookup
- Import Column
- Percentage Sampling
- Pivot
- Row sampling
- Term Extraction
- Term Lookup
- Unpivot
- 2.1. Common – Najdeme zde nejpoužívanější nástroje pro datové transformace, např Derived Column (přidání nějakého sloupce), Data Conversion (změna definice sloupců), atp
Jednotlivým komponentám se budu věnovat v samostatných článcích.
Příklad použití – Load dat z Excelu do SQL Server tabulky
Úkolem bude v našem SSIS package upravit Data flow task tak, aby dělal následující:
- Na C:\Biportal_Data máme excel, který se jmenuje “Source_Data.xls” na který se chceme připojit
- Do dat přidáme nový sloupec Date_Timestamp jako časové razítko
- Výsledek nahrajeme do SQL Server – localhost, tabulka dbo.Excel_Data
Zdrojová data vypadají takto:
Cílová tabulka v SQL Server vypadá takto:
Řešení:
1) Nejprve se potřebujeme napojit na data v Excelu
- V other Sources přetáhneme do prostoru data flow Excel Source
- Následně klikneme na New connection manager (protože connection na Excel ještě nemáme vytvořen)
- Nastavíme cestu na soubor C:\Biportal_Data\Source_Data.xls a potvrdíme
- Dále vybereme Sheet, na kterém se nacházejí data
- Poté klikneme v levém panelu na Columns a podíváme se jestli výsledek obsahuje náš sloupec s daty
- Vše je v pořádku a můžeme kliknout OK, Data source máme nachystán
2) Nyní chceme do našich zdrojových dat doplnit nový sloupec s časovým razítkem Date_Timestamp
- Z SSIS toolboxu vybereme komponentu Derived Column a přetáhneme do data flow. Šipou obě komponentý propojíme.
- Poklikáme na derived column a nastavíme jej tak jako na screenshotu. Tímto je hotovo a nový sloupec jsme přilepili k původním datům
3) Nachystáme si Data Destination providera – naše zdrojová data doplněná o derived column potřebujeme nahrát na SQL Server
- Podobně jako u Excel source (krok 1) si připravíme i destination. V other destination vybereme OLE DB Destination a přetáhneme ji do prostoru Data Flow. Následně ji otevřeme a nastavíme connection na cílový server (localhost) a databázi (biportal). Jako “Name of the table or view” vybereme naši cílovou tabulku dbo.Excel_Data
- Následně musíme namapovat sloupce ze zdrojového souboru na cílovou tabulku. Klikneme tedy na “Mappings”. Pokud se názvy sloupců ve zdrojovém excelu a v cílové tabulce jmenují stejně, tak OLE DB destination provider pochopí, že je má spojit. Pokud by se jmenovaly jinak, tak je potřeba sloupce mezi sebou namapovat ručně přes černé šipky. V našem případě jsme sloupce pojmenovali stejně a není tedy potřeba nic dělat. Potvrdíme OK
Naše výsledné Control flow a data flow tedy vypadá takto
Můžeme tedy celý SSIS package spustit a podíváme se na výsledek
Data byla úspěšně doručena do SQL Server tabulky