1 / 30

The ABC of ETL with SSIS

The ABC of ETL with SSIS. James Beresford www.bimonkey.com. AIM. To cover the basics of ETL using Microsoft BI tools. Tools Database Structure SSIS Package Level Components. Agenda. SQL Server Tools for ETL. Database Engine stores data – optional Integration Server

catrin
Download Presentation

The ABC of ETL with SSIS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The ABC of ETL with SSIS James Beresford www.bimonkey.com

  2. AIM To cover the basics of ETL using Microsoft BI tools

  3. Tools • Database Structure • SSIS • Package Level • Components Agenda

  4. SQL Server Tools for ETL

  5. Database Engine • stores data – optional • Integration Server • executes packages • SQL Agent • schedules packages - optional SQL Server

  6. GUI Tools – SSMS

  7. GUI Tools – BIDS Where you will spend 95% of your time

  8. GUI Tools – SQL Profiler

  9. Command Line dtutil - movesdtexec - does

  10. Database Structure

  11. Audit and Control Control Structures Control Mechanisms Logging

  12. Deployment BIDS doesn’t work properly!Use dtutil batch scripts

  13. Deployment File SystemvsPackage StorevsSQL Server

  14. Execution Create an Execution AccountDo not use SA account

  15. SSIS functional units are Packages… • …then within Packages: • Control Flow • Data Flow SSIS

  16. Connection managers • Checkpoints • Event Handlers • Configurations • Variables & Expressions • Logging SSIS Packages

  17. Connection Managers Components which refer to a data source or destination

  18. Key Properties at Package Level: • CheckpointFileName • CheckpointUsage • SaveCheckpoints • FailPackageOnFailure = True for all restart points Checkpoints

  19. Event Handlers • As packages execute, a series of events occur, e.g: • Validation Completing • Errors • Variable values changing

  20. Configurations are External Sources of Package Execution Settings Configurations

  21. Variables provide changeable values to be used by packages SSIS has its own expression language Variables & Expressions

  22. Logging Logging Providers record execution details

  23. Control Flow & Data Flow SSIS Packages

  24. Control Flow Mission Control!

  25. Containers Precedence Constraints Non Data Tasks Control Flow

  26. Provide looping Support Transactions Containers

  27. Control flow of package Success / Failure & Expressions Precedence Constraints

  28. Execute SQL • Execute Package • FTP • Send Mail • Process SSAS Objects • etc… Non Data Tasks

  29. Sources and Destinations • SCD • Lookup • Data Conversion • Derived Columns • OLEDB Command • Custom components Data Flow

  30. learnintegrationservices.com http://blogs.conchango.com/jamiethomson/ Learning Resources

More Related