SSIS | Data Flow Task – ETL Datové Toky s příkladem

Poslední aktualizace:

V minulém díle jsem popsal funkci Control Flow v SSIS balíčku. Pomocí control flow dokážeme řídit to, co SSIS package 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.

Dat flow task - example

Data flow task na screenshotu je uprostřed, pojmenovaný jako “ETL”. Tento task si můžeme rozkliknout a podívat se dovnitř.

Data flow task - detail

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

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:

Data Flow - example - load from excel to SQL Server

Cílová tabulka v SQL Server vypadá takto:

DAta flow example - oad data from excel to SQL server - destination

Ř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

Excel connection manager - data flow example

  • Dále vybereme Sheet, na kterém se nacházejí data

Data flow example - create excel connection manager - sheet

  • Poté klikneme v levém panelu na Columns a podíváme se jestli výsledek obsahuje náš sloupec s daty

data flow example - excel connection manager - source editor - columns

  • 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.

data flow example - adding derived column

  • 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

data flow example - derived column settings

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

Data flow example - ole db destination settings

  • 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

Data flow example - destination settings - mapping

Naše výsledné Control flow a data flow tedy vypadá takto

Control flow example - final  Data flow example - final

Můžeme tedy celý SSIS package spustit a podíváme se na výsledek

Control flow example - final after package execution  data flow example - final after package execution

Data byla úspěšně doručena do SQL Server tabulky

Data flow example - final check

SSIS | Data Flow Task – ETL Datové Toky s příkladem
5 (100%) 2 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.