SQL Server 2012 Integration Services
Werbung
SQL Server 2012 Integration Services Überblick und Best Practices Robert Panther, Senior Consultant 15.03.2013 © CGI Group Inc. Vorstellung Name Robert Panther Rolle Senior Consultant, Logica now a part of CGI Fachliche Schwerpunkte • Datenbanken: Design, Implementierung & Administration (insbesondere Performance Optimierung und ETL mit SSIS) • Mobile Anwendungen: Design & Implementierung • Webanwendungen: Implementierung & Administration Technologien • Microsoft SQL Server • Windows Mobile • .NET (VB .NET & C#), ASP, ASP.NET Methoden Datenbankdesign, Objektorientierte Programmierung, Strukturierte Programmierung, Schulungskonzeption & Durchführung, Projektleitung Zertifikate Informatik Diplom (FH) MCTS SQL Server 2005 Implementation & Maintenance MCTS SQL Server 2005 Business Intelligence MCTS SQL Server 2008 Implementation & Maintenance MCITP SQL Server 2008 Developer Spezielles Autor von mehreren Fachbüchern und Fachartikeln Leitung der SQL Server Expert Group bei Logica / CGI regelmäßiger Speaker auf Fachkonferenzen aktives PASS-Mitglied 2 Agenda Überblick SSIS Neuerungen in SSIS 2012 Best Practices Allgemeine Konventionen Struktur von SSIS-Projekten Namenskonventionen Lösungen für bekannte Probleme Fazit Was fehlt noch? Links & Literatur 3 Überblick SSIS Überblick SSIS Was ist SSIS? • SSIS = SQL Server Integration Services • SSIS ist ein ETL-Tool • E – Extract : Extrahieren von Daten • T – Transform : Transformieren von Daten • L – Load: Laden von Daten • SSIS ist Bestandteil des SQL Servers (bzw. dessen BI-Tools) • SSIS : SQL Server Integration Services • SSAS : SQL Server Analysis Services • SSRS : SQL Server Reporting Services 5 Überblick SSIS Typische Verwendungszwecke von SSIS: • Befüllen von Data Warehouses • Datenmigrationen • regelmäßige Datenabgleiche • Export-Batches • etc. Historie von SSIS: • SQL Server 2000 : DTS (Vorgänger von SSIS) • SQL 2005/2008/2008 (R2) : SSIS (jeweils leicht verbessert) • SQL 2012 : SSIS stark überarbeitet 6 Überblick SSIS Entwicklung von SSIS-Paketen: • Bis SQL 2008 R2 mit dem Business Intelligence Development Studio • Seit SQL 2012 mit den SQL Server Data Tools Ausführung von SSIS-Paketen: • in Entwicklungsumgebung (zum Testen und Debuggen) • explizit über das SQL Server Management Studio • geplant über den SQL Server Agent • über das Execute Package Utility (DTEXEC/DTEXECUI) • SSIS läuft als eigener Prozess 7 Überblick SSIS – Projekte und Pakete Projektmappen: • können mehrere Projekte beinhalten • die Projekte können unterschiedliche Typen haben SSIS-Projekte: • können mehrere SSIS-Pakete enthalten SSIS-Pakete: • sind vergleichbar mit einzelnen Programmen • ein SSIS-Paket wird komplett in einer DTSX-Datei gespeichert (XML-Format) • werden in der Quelltextverwaltung komplett ausgecheckt 8 Überblick SSIS – Ablaufsteuerung • die Grundstruktur eines SSIS-Pakets wird über die • • • • Ablaufsteuerung (engl. Control Flow) festgelegt mit einem Programmablaufplan vergleichbar setzt sich aus Ablaufsteuerungselementen zusammen • Verarbeitungstasks • Strukturelemente (Schleifen- und Sequenzcontainer) • Sonderfall: Wartungsplantasks Bestimmte Tasks können separat definierte Module aufrufen: • Datenflusstask • ‚Paket ausführen‘-Task • ‚Prozess ausführen‘-Task • ‚SQL ausführen‘-Task In der Ablaufsteuerung findet auch das Transaktionshandling statt 9 Überblick SSIS – Ablaufsteuerung Ein einfacher Ablauf: 10 Überblick SSIS – Ablaufsteuerung Ausführungsreihenfolge: • nicht verbundene Tasks werden parallel ausgeführt • mit Pfeilen verbundene Tasks werden in der Reihenfolge der Pfeilrichtung ausgeführt • mehrere Ausgangspfeile bedeuten parallele Ausführung • mehrere Eingangspfeile bedeuten alle vorangegangenen Schritte müssen fertig sein (kann auch anders eingestellt werden) • Verbindungspfeile lassen sich mit Einschränkungen (Erfolg/Fehler/Beendigung) und/oder Ausdrücken „verfeinern“ • Tasks können mit Sequenzcontainern gruppiert werden • Tasks können mit For- bzw. Foreach-Schleifencontainern mehrfach durchlaufen werden 11 Überblick SSIS – Datenfluss • der wichtigste Task in der Ablaufsteuerung ist der Datenflusstask • ein Datenfluss stellt den eigentlichen ETL-Prozess dar • Datenflüsse werden in einer separaten Ansicht entworfen und bestehen normalerweise aus drei Komponenten: • Datenflussquelle (Extract) • Datenflusstransformation (Transform) • Datenflussziel (Load) • Datenflüsse werden im Prinzip in Pfeilrichtung ausgeführt • weder satzweise noch komplett pro Schritt, sondern in Blöcken • Ziel dabei ist eine größtmögliche Parallelität 12 Überblick SSIS – Datenfluss Ein einfacher Datenfluss: 13 Überblick SSIS – Datenfluss Datenquellen und –ziele: • Es gibt vielfältige Datenquellen & -ziele: • Flatfiles • Excel-Tabellen • XML-Dateien • ADO .NET (SQL Server, Oracle, ODBC etc.) • OLE DB (SQL Server, Oracle, Jet Engine/MS Access etc.) • Wird eine Datenbank als Quelle genutzt, kann wahlweise eine Tabelle/Sicht oder auch eine SQL-Abfrage angegeben werden 14 Überblick SSIS – Datenfluss Transformationen: • Transformationen verändern die eingehenden Daten • Transformationen können auch die Anzahl der Zeilen- und Spalten verändern • einige Transformationstypen haben mehrere Eingänge • UNION ALL (UNION von beliebig vielen – auch unsortierten - Eingängen) • Zusammenführen (UNION von zwei sortierten Eingängen) • Zusammenführungsverknüpfung (JOIN von zwei sortierten Eingängen) • einige Transformationstypen haben mehrere Ausgänge • für korrekte und fehlerhafte Zeilen • Multicast (mehrere identische Ausgänge) • für komplexe Transformationen können Skriptkomponenten genutzt werden, deren Code pro Zeile des Datenflusses durchlaufen wird 15 Neuerungen in SSIS 2012 Neuerungen in SSIS 2012 Allgemein • nutzt VS 2010 IDE (u.A. Zoom und frei verschiebbare Fenster) • APIs für Erweiterungen • Scope von Variablen nachträglich anpassen • besser lesbares XML • endlich ein Undo im Designer! • copy & paste verbessert • Autosave & Recovery • kein DTS-Support mehr 17 Neuerungen in SSIS 2012 SSIS-Server • verwaltet komplette Projekte, nicht nur einzelne Pakete • besitzt eigene Katalog-DB (SSISDB) • Pakete über SSIS-Dienst remote ausführbar • Integration Services-Dashboard • Verwaltung und Ausführung über SPs • eigene Performance-Counter 18 Neuerungen in SSIS 2012 SSIS-Projekte • Connection Manager auf Projektebene (keine Shared DataSource mehr) • Konfiguration mit Hilfe von Projektparametern • Deployment in Form von .ispac-Dateien Ablaufsteuerung • Execute Package wahlweise aus Projektreferenz (ohne expliziten Connection Manager) • Expression Task (Ausdruckstask) (z.B. zum Vorbelegen von Variablen) 19 Neuerungen in SSIS 2012 Datenfluss • Source/Destination Assistent • Work Offline • mapping changes reparieren (z.B. geänderte Spaltennamen) • Pivot über Spaltenbezeichnungen (nicht mehr LineageIDs) • gruppieren im DF (mit collapse & expand) • Merge & Merge Join (gedrosselt wenn ein Input langsam, spart Ressourcen) • vereinfachter Datenviewer (keine graphische Darstellung) • spezielle Data Quality-Transformationen (DQS-Bereinigung) 20 Best Practices SSIS Best Practices – Allgemeine Konventionen Allgemeine Konventionen • Pakete sollten stets wiederholt startbar sein • zu Beginn “aufräumen” • evtl. Transaktionen nutzen • Fehlerausgaben nutzen • Im einfachsten Fall in Multicast Container • Im Idealfall mit Fehlerprotokollierung • Protokollierung in zusätzliche Tabellen • z.B. für komplexe Mappings • Konfigurierbarkeit über Variablen und Expressions 22 SSIS Best Practices – Allgemeine Konventionen Konventionen zur besseren Lesbarkeit • Ablaufsteuerung und Datenfluss von oben nach unten • Notwendigkeit zum horizontalen Scrollen vermeiden • Fehlerbehandlung im Datenfluss zur Seite führen 23 SSIS Best Practices – Struktur von Projekten Struktur von SSIS-Projekten • Ein Paket pro Datenquelle oder Datenziel • Ein Master-Paket, das Unterpakete aufruft • Konfiguration der Variablen • Allgemeine Vor- und Nachbereitung • Fremdschlüssel de- & re-aktivieren • Sequenzcontainer zur Strukturierung 24 SSIS Best Practices – Struktur von Paketen Struktur von SSIS-Paketen • Vorbereitungsschritte • (nicht gruppierte) Indizes deaktivieren • Zieltabellen leeren • Datenfluss • Nachbereitungsschritte • Indizes neu erstellen 25 SSIS Best Practices – Namenskonventionen Namenskonventionen • Name des Pakets entspricht Quelle oder Ziel (einheitlich!) • Kurze Präfixe für Tasks und Container verwenden, z.B. … • SCR für Script Tasks • SQL für SQL Tasks • EP für Execute Package • LKP für Suche (von Lookup) • ODB für OLE DB Quellen & Ziele 26 Lösungen für bekannte Probleme Lösungen für bekannte Probleme Problem: Bestimmte Datenquellen (z.B. Excel, Access) sind nicht nutzbar. Lösung: • Projekt auf 32Bit Runtime umstellen. 28 Lösungen für bekannte Probleme Problem: Ziel im Datenfluss lässt bei einer fehlerhaften Zeile ganzen Block von Zeilen in die Fehlerausgabe laufen. Lösung: 1. OLE DB Ziel im Modus “schnelles Laden” mit Fehlerausgabe versehen. 2. Fehlerhafte Zeilen noch einmal im “langsamen” Modus schreiben. 3. Weitere Fehlerausgabe mit Behandlung für die final fehlerhaften Zeilen. 29 Lösungen für bekannte Probleme 30 Lösungen für bekannte Probleme Problem: Datenfluss schreibt in zwei Zieltabellen, von denen die eine auf die andere verweist. Lösungsvarianten: a) Für Primär- und Fremdschlüssel werden vorher bekannte IDs verwendet (evtl. vorher maximale ID ermitteln). b) Für Primär- und Fremdschlüssel werden im Datenfluss definierte Werte (z.B. in der Quellabfrage erzeugte Guids) verwendet. 31 Lösungen für bekannte Probleme Problem: Datenfluss schreibt in zwei Zieltabellen, von denen die eine auf die andere verweist. Die Tabellen verwenden IDs mit Identitätsspezifikation. Lösungsvarianten: a) In erste Tabelle wird per Stored Procedure geschrieben und die ID der neuen Zeile per Output-Parameter in Datenfluss zurückgegeben. b) Foreign Key-Spalte wird mit temporären Wert befüllt, der nach dem Datenfluss per Nachbereitungsskript durch die korrekte ID ersetzt wird. 32 Fazit Fazit – SSIS allgemein • Selbst für einfache Datentransfers eignet sich SSIS hervorragend! • Trotz der einfachen visuellen Darstellung darf man sich nicht darüber hinweg täuschen lassen, dass mit SSIS sehr komplexe Abläufe realisierbar sind! • SSIS ist vielseitig einsetzbar • Mit SSIS können typische Batch-Läufe implementiert werden! • SSIS ist nahezu beliebig erweiterbar: • Datenquellen, Tasks und Komponenten von Drittanbietern • eigenen .NET-Code • beliebige .NET-Assemblys 34 Fazit – SSIS allgemein Warum SSIS und nicht .NET oder SQL? • optimiert für Massendatenverarbeitung • Datenquellen und –ziele können auf unterschiedlichen Servern liegen • Fehlerbehandlung und Debugging (Umleitung von fehlerhaften Zeilen) • Ausnutzung der Parallelverarbeitung • bei Bedarf können .NET und SQL eingebunden werden 35 Fazit – SSIS 2012 Die SQL Server Integration Services sind endlich erwachsen geworden. Die meisten Unzulänglichkeiten der Vorgängerversionen wurden beseitigt. An einigen Stellen gibt es aber noch wie vor Raum für Verbesserungen. Was fehlt noch? • Unterstützung bei der Ausrichtung von Objekten im Designer • Auskommentieren von Schritten im Datenfluss (z.B. Datenziel) • Strukturierung von SSIS-Paketen in der Solution (Unterordner, Sortierung etc.) • komfortablere SQL-Editoren • Zoom in Fehleransicht • Datenziel mit OUTPUT (inserted) 36 Links & Literatur Links zum Thema SSIS: • SSIS Online-Dokumentation: http://msdn.microsoft.com/de-de/library/ms141026.aspx • Ressourcen für SSIS-Entwickler und IT-Spezialisten: http://msdn.microsoft.com/de-de/sqlserver/cc511477.aspx Links des Referenten: • SQL Server Blog: http://pantheronsql.wordpress.com • Logica / CGI: http://www.logica.de / http://www.cgi.com Literatur: • Microsoft SQL Server Integration Services: Erstellen von Datenintegrations- und Datentransformationslösungen auf Unternehmensebene Bernd Jungbluth, Microsoft Press, 2011 (aktualisierte Auflage für SQL 2012 für Juni 2013 geplant) 37 Ein kleines Anliegen in eigener Sache … Zur Verstärkung unseres SQL Server Teams am Standort Frankfurt/Sulzbach suchen wir … Datenbankarchitekten Microsoft SQL Server (m/w) Sie werden für die Beratung, Konzeption, Projektleitung, aber auch für die Realisierung von anspruchsvollen Lösungen verantwortlich sein, deren technologischer Schwerpunkt im Umfeld Microsoft SQL Server liegt. Sie helfen unseren Klienten, unternehmenskritische Anwendungen hochverfügbar, sicher und kostengünstig zu gestalten. Dabei setzen Sie moderne Methoden und Techniken ein und integrieren sie in die Unternehmenslandschaft. Auf diese Weise tragen Sie dazu bei, dass zukunftsfähige Plattformen entstehen, auf denen Business und E-Business sicher und stabil betrieben werden können. Interesse? Dann sprechen Sie mich an, oder schicken Sie mir eine Mail an [email protected] 38 Vielen Dank für Ihre Aufmerksamkeit! Robert Panther [email protected] 39
