Integration Services 2008

1. Integration Services 2008 Evolution statt RevolutionWillfried FärberMVP SQL Server

2. AGENDA • SSIS 2008: Alles wird besser • Der erste Schritt: „Import und Export Assistent“ • Eine neue Möglichkeit in der Script Task • Für Profis: Die neue Architektur des Datenflusses • Datenprofiling • Änderungen beim Lookup • SCD für Fortgeschrittene

3. AGENDA • Erfassung von Datenänderungen • Dump für alle Fälle • Fragen und Antworten

4. Vorbemerkungen • Das sollte kein Neuland sein … • Sie wissen was ETL ist • Sie kennen bereits SSIS aus dem SQL Server 2005 • Sie wissen warum SSIS ein wichtiges Werkzeug ist • Ich zeige Ihnen Betacode • Wahrscheinlich reicht die Zeit nicht ….. • Sie bekommen die Folien zum Nachlesen

5. Zusätzliche neue Funktionalitäten • Noch nicht offiziell angekündigt

6. Zum Beispiel: Enumeratoren • ForEach Database Enumerator • Benutzt OLEDB alsVerbindungsmanager • Server derdurchlaufenwerdensollkannkonfiguriertwerden • SehrinteressantesBenutzerinterface • NichtjedeInnovationsetztsichdurch

7. DB Enumeration Types DBENUM_CUSTOM = 0 DBENUM_SYSTEMONLY = 1 DBENUM_USERONLY = 2 DBENUM_ALL = 3

8. Neues Datenziel • Die „DataReader“ Datenquelle verschwindet • Heißt jetzt „ADO.Net“ Datenquelle • Es gibt jetzt auch ein ADO.Net Datenziel • Kein Scriptkode für ODBC Datenquellen mehr notwendig • ADO.Datenquelle / Ziel ist komfortabel • Allerdings 5 bis 10 Prozent langsamer als OLE.DB • Bestimme Tasks brauchen explizit die ADO.Net Datenquelle

9. ADO.Net Source Adapter

10. ADO.Net Destination Adapter fürODBC

11. Der neue „Import und Export Assistent“ • Für einfache Import- und Exportvorgänge das Richtige • Alle Datenquellen und Ziele aus SSIS werden unterstützt • Komplett Assistenten gesteuert • Auch beim Kopieren von vielen Tabellen effektiv • Ganz geringe Eingriffsmöglichkeiten für eine Transformation

12. DTS Wizard Demo

14. Die neue Script Umgebung • Die VSA Umgebung wird durch die VSTA Umgebung ersetzt. • Jetzt ist es wirklich Visual Studio • Unterstützte Sprachen • Visual Basic • C# • Referenzen zu allen .Net Assemblies einfach möglich • Für Webservices kann einfach eine Proxie Klasse erzeugt werden • Codesnippets auch in SSIS möglich

15. VSTA ersetzt VSA Demo Die neue Scriptumgebung

16. C# Scrip Transform AufrufeinesWebservices

17. Die neue Architektur des Datenflusses • Jetzt • Neue Implementierung des Thread Schedulers • Verbesserte Parallelisierung der Verarbeitung im Datenfluss • Deutliche Verbesserung bei der Performance und bei der Ausnutzung vorhandener Prozessoren • Je nach Design des Datenflusses um bis zu 50 % Leistungssteigerung • Weniger Zeitaufwand für manuelles Tuning • Der Datenfluss „funktioniert“ einfach ….

18. Komplexer Datenfluss

19. AusführungsplanDatenfluss

21. Ausnutzungder CPUs SSIS 2005 SSIS 2008 Biszu 80% schneller* * Dieser Wert kannnatürlichvariieren

22. Datenprofiling • Wie sehen die Daten wirklich aus • Genaues kennen der Daten ist im ETL Prozess sehr wichtig • Bisher durch Abfragen auf die Daten • Datenquellensicht konnte für einfache Fälle verwendet werden • Jetzt gibt es eine eigene „Data Profiling“ Task • Erfordert eine ADO.Net Datenquelle • Funktioniert nur mit dem SQL Server

23. Die Data Profiling Task Demo

24. Daten Profiling Task Oberfläche

25. Option: Quick Profile

26. VerteilungderWerte in einerSpalte

27. WielvielWerte in einerSpaltesind NULL

28. Column Pattern Profile

29. Die Suche Transformation • Die Referenzdaten können von jeder beliebigen Datenquelle geladen werden • Der Cache für die Suche-Transformation kann explizit gespeichert werden • Der Cache kann zu jedem beliebigen Zeitpunkt geladen werden. • Kann sehr schnell gelesen und geschrieben werden (Rohdatenformat mit Ergänzungen) • Es gibt einen eigenen „Nicht gefunden“ Datenpfad

30. SSIS Suche Transformation • ErsterSchritt • Der Cache wird von einerbeliebigenDatenquellegefüllt und gespeichert • WeitereVerarbeitung • Der Cache wird von der Platte geladen Fact Sales customer .csv Einlesen des Caches von der Platte oderNutzungimSpeicher • Ablegen des Caches auf der Platte oderimSpeicher Der Cache bleibtbestehen und kannmehrfachgenutztwerden

31. Die verbesserte Suche Transformation Demo

32. SCD für Fortgeschrittene • Der „Langsam veränderliche Dimension“ Assistent hat einen schlechten Ruf • Kann durch geschickte Ausnutzung der Möglichkeiten auch mit großen Datenmengen umgehen • Allerdings ist dafür Handarbeit notwendig • Der „Merge“ Befehl des SQL Servers 2008 kann für SCD Typ 1 und SCD Typ2 verwendet werden. • Aufruf des TSQL Befehls in der Ablaufsteuerung • „Ausführen SQL“ Task

33. Der Mergebefehl • Steht in jeder Version des SQL Servers 2008 zur Verfügung • Kann natürlich auch außerhalb von SSIS verwendet werden • Führt Update, Insert oder Delete innerhalb eines Befehls aus • Da nur ein Durchlauf durch die Zieltabelle notwendig ist, meistens deutlich effektiver als getrennte Befehle • Kann bei größeren Datenmengen die SCD Transformation ersetzen

34. MERGE Syntax [ WITH <common_table_expression> [ ,…n ] ] MERGE [ TOP (expression) [ PERCENT ] ] [ INTO ] <target_table> [ [ AS ]table_alias ] [ WITH( <merge_hint> ) ] USING <table_source> ON <search_condition> <merge_clause> [ …n ] [ OUTPUT <dml_select_list>] [ OPTION ( <query_hint> [ ,…n ] ) ] ; <merge_clause>:= { WHEN MATCHED [ AND <search_condition> ] THEN { UPDATE SET <set_clause> | DELETE} | WHEN [ TARGET ] NOT MATCHED [ AND <search_condition> ] THEN INSERT [ (column_list) ] { VALUES (values_list) | DEFAULT VALUES } | WHEN SOURCE NOT MATCHED [ AND <search_condition> ] THEN { UPDATE SET <set_clause> | DELETE } } ■SQL Standard MERGE Extensions ■Proprietary Syntax Extensions

35. Example: Daten Synchronisierung CREATE TABLE Original (o_pk INT PRIMARY KEY, o_name VARCHAR(10), o_number INT); CREATE TABLE Replica (r_pk INT PRIMARY KEY, r_name VARCHAR(10), r_number INT); CREATE PROCEDURE usp_SyncReplica AS MERGE Replica USING Original ON o_pk = r_pk WHEN MATCHED AND (o_name != r_name OR o_number != r_number) THEN -- Row exists but data differs UPDATE SET r_name = o_name, r_number = o_number WHEN SOURCE NOT MATCHED THEN -- Row exists in Replica but not Original DELETE WHEN NOT MATCHED THEN -- Row exists in Original but not Replica INSERT VALUES (o_pk, o_name, o_number) OUTPUT $action, inserted.r_pk, deleted.r_pk;

36. Example: UPSERT – SQL 2005 CREATE TABLE MyTable(pk INT PRIMARY KEY, name VARCHAR(10), number INT); CREATE PROCEDURE Legacy_Upsert_1 @pk INT, @name VARCHAR(10), @number INT AS UPDATE MyTable SET name = @name, number = @number WHERE pk = @pk -- Row is updated even if all the values are correct IF (@@ROWCOUNT = 0) INSERT MyTable VALUES(@pk, @name, @number) -- If row did not exist, a second statement execution is required CREATE PROCEDURE Legacy_Upsert_2 @pk INT, @name VARCHAR(10), @number INT AS -- Updating only if any column needs to be changed requires an extra statement IF EXISTS (SELECT 1 FROM MyTable WHERE pk = @pk) UPDATE MyTable SET name = @name, number = @number WHERE pk = @pk AND (name <> @name OR number <> @number) ELSE INSERT MyTable VALUES(@pk, @name, @number)

37. Example: Merge mit SQL Server 2008 CREATE PROCEDURE Merge_Upsert @pk INT, @name VARCHAR(10), @number INT AS MERGE MyTable t USING (SELECT @pk AS pk, @name AS name, @number AS number) s ON t.pk = s.pk WHEN MATCHED AND (t.name <> s.name OR t.number <> s.number) THEN UPDATE SET name = s.name, number = s.number WHEN NOT MATCHED THEN INSERT VALUES (pk, name, number);

38. Beispiel: Aktienhandel CREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK (Qty > 0)); CREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY, Delta INT CHECK (Delta <> 0)); INSERT Stock VALUES('MSFT', 10), ('BOEING', 5); INSERT Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3); MERGE Stock as S USING Trades as T ON S.Stock = T.Stock WHEN MATCHED AND (S.Qty + T.Delta = 0) THEN DELETE -- delete stock if entirely sold WHEN MATCHED THEN -- delete takes precedence on update UPDATE SET S.Qty += T.Delta WHEN NOT MATCHED THEN INSERT VALUES (Stock, Delta); -- BOEING is deleted, GE inserted, MSFT updated

39. Inkrementelle Verarbeitung • Das Schlüsselwort heißt Change Data Capture • Nur geänderte Datensätze werden verarbeitet • Steht im SQL Server 2008 zur Verfügung • Kann natürlich auch für SSIS genutzt werden

40. Dumpfiles innerhalb von SSIS • Schalter bei DTExec und DTExecUI • /Dump • Erzeugt immer einen Dump bei einem Fehler • /DumpOnError • Erzeugt nur einen Dump bei einem oder mehreren bestimmten Fehlern • Eintrag in der Registry • Dump wird in zwei Formaten erzeugt • *.mdmp – binäres File für den Microsoft Support • *.tmp – textbasierter Dump

41. Dumpfiles innerhalb von SSIS • Gleiche Technik wie Dr. Watson • Der Microsoft Support kann jetzt wirklich schnell helfen • Für schwierige Fälle • Beim Resourcenverbrauch • Bei logischen Problemen • Bei hartnäckigen Problemen mit der Laufzeitumgebung • Das Dumpfile legt Ihre Geheimnisse auf den Tisch

42. Dumpfile in SSIS • Dumpfile wird unter %ProgramFiles%\Microsoft SQL Server\100\Shared\ErrorDumps abgelegt • *.tmp Format ist Text • *.mdmp für den Microsoft Support • Informationen über • Environment • Dynamic-Link Libraries • Letzten Meldungen aus einem Ringpuffer

43. Zusammenfassung • Es hat sich mehr getan, als man glaubt • SSIS profitiert von Erweiterungen in der Relationalen Datenbank • Change Data Capture • Merge • Jede Menge „unsichtbare“ Verbesserungen • Optimierter Datenfluss • Dumpfile • Sichtbare Änderungen • Verbesserungen bei der Suche Transformation • Neue Scriptumgebung • …..

44. Fragen und Antworten • Willfried FärberMVP für den SQL Serverwfaerber@web.de

45. Sie sind nicht alle … SQLPasse.V Deutschland http://www.sqlpass.de

46. AsktheExperts Wir freuen uns auf Ihre Fragen: Technische Experten stehen Ihnen während der gesamten Veranstaltung in der Haupthalle zur Verfügung.

47. MehrInformationen • Microsoft BIwww.microsoft.com/bi • SQL Server Integration Services http://www.microsoft.com/sql/technologies/integration/default.mspx • Guided Tours for SSIS http://www.microsoft.com/sql/technologies/integration/tours.mspx • Technical Portal for SSIS http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/ssisvcs.mspx • Developer Portal for SSIS http://msdn2.microsoft.com/en-us/sql/aa336312.aspx

48. Resources Technical Communities, Webcasts, Blogs, Chats & User Groups http://www.microsoft.com/communities/default.mspx Microsoft Learning and Certification http://www.microsoft.com/learning/default.mspx Microsoft Developer Network (MSDN) & TechNet http://microsoft.com/msdn http://microsoft.com/technet Trial Software and Virtual Labs http://www.microsoft.com/technet/downloads/trials/default.mspx

49. © 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.