FDUG December: Integration Services Fundamentals

This presentation will cover the fundamentals of Microsoft Integration Services (SSIS) tool which is a powerful component of SQL server for moving, manipulating and integrating data. SSIS is platform for data integration and workflow applications. The development tools use for building packages is Business Development Studio and it will be used for the demos during the talk (BIDS). Gaetan has been using this tool for over 6 months and will cover the control flow tasks, data flow task, data flow transformation task, expressions, scripts (using C# code) and more. The presentation will provide real life scenario in the context of data migration (i.e. using ETL for moving data from one system to another)..

Transcript

1. Fredericton User Group http://frederictonug.net
2. Fredericton User Group Today’s Topic Wednesday December 12nd, 2013 SSIS - Integration Services Fundamentals Speaker: Gaetan Savoie
3. Fredericton User Group SQL SERVER INTEGRATION SERVICES AGENDA 1. SSIS Overview 2. Control Flow Tasks 3. Data Flow Tasks and transformations 4. Expressions and Scripts 5. Package Reliability (Error, Checkpoints, etc.) 6. Exercise: Build a package to migrate data. 7. Questions
4. Fredericton User Group 1. SSIS OVERVIEW COMMON USES • Populating data warehouse (ETL) • Cleaning and standardizing data. • Merging data from multiple data stores. • Automating administrative tasks. • Data migration – I’m currently doing this on my current engagement!
5. Fredericton User Group 1. SSIS OVERVIEW - ARCHITECTURE
6. Fredericton User Group 1. SSIS OVERVIEW
7. Fredericton User Group 1. SSIS OVERVIEW BIDS Project Type • Integration Service Connection Project Wizard • Integration Sevices Project Integration Service Project Items • Package – DTSX File • Data Source – DS file • Data Source View BIDS INTRO DEMO
8. Fredericton User Group 2. Control Flow Tasks SQL Server 2008 • 28 control flow items and 11 Maintenance flow tasks 9 categories: • Container Tasks: Use for structure or repetitive task. • Data Flow Tasks: Move data to a destination and transform. • Data Preparation Tasks: Retrieve data and validate. • Workflow Tasks: Communicate with other processes. • SQL Server Tasks: Copy, modify, delete SQL Objects. • WMI Tasks: WQL Queries against Windows Management Instrumentation • Scripting Tasks: Execute functions not supported out of the box. • Backward Compatibility Tasks: DTS2000,ActiveX • SQL Server Analysis Services Tasks: Analysis Services objects
9. Fredericton User Group 2. Control Flow Tasks
10. Fredericton User Group 2. Control Flow Tasks
11. Fredericton User Group 2. Control Flow Tasks
12. Fredericton User Group 2. Control Flow Tasks
13. Fredericton User Group 2. Control Flow Tasks
14. Fredericton User Group 2. Control Flow Tasks
15. Fredericton User Group 3. Data Flow Tasks and transformations
16. Fredericton User Group 3. Data Flow Tasks and transformations
17. Fredericton User Group 3. Data Flow Tasks and transformations
18. Fredericton User Group 3. Data Flow Tasks and transformations
19. Fredericton User Group 3. Data Flow Tasks and transformations
20. Fredericton User Group 3. Data Flow Tasks and transformations
21. Fredericton User Group 3. Data Flow Tasks and transformations
22. Fredericton User Group 4. Expressions and Scripts
23. Fredericton User Group 4. Expressions and Scripts
24. Fredericton User Group 4. Expressions and Scripts
25. Fredericton User Group 4. Expressions and Scripts
26. Fredericton User Group 4. Expressions and Scripts
27. Fredericton User Group 4. Expressions and Scripts
28. Fredericton User Group 4. Expressions and Scripts
29. Fredericton User Group 4. Expressions and Scripts
30. Fredericton User Group 4. Expressions and Scripts
31. Fredericton User Group 4. Expressions and Scripts
32. Fredericton User Group 4. Expressions and Scripts
33. Fredericton User Group 4. Expressions and Scripts
34. Fredericton User Group 5. Package Reliability Multiple Provider Type • SQL Server • Windows Event Log • Text File • XML File • SQL Server Profiler Use Log Methods in Script Task for custom logging
35. Fredericton User Group 5. Package Reliability
36. Fredericton User Group 5. Package Reliability
37. Fredericton User Group 5. Package Reliability
38. Fredericton User Group 5. Package Reliability
39. Fredericton User Group 5. Package Reliability
40. Fredericton User Group 5. Package Reliability
41. Fredericton User Group 6. DEMO
42. Fredericton User Group 7. Questions