1 / 13

Solving Problems in ETL using SSIS

Solving Problems in ETL using SSIS. Allan Mitchell SQL Server MVP www.Konesans.com. Why am I here. Worked with DTS since the beginning Worked onsite with SSIS before RTM Co-Author on Wrox Professional SSIS Book Run www.SQLDTS.com and www.SQLIS.com I am not Microsoft.

amalie
Télécharger la présentation

Solving Problems in ETL using 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. Solving Problems in ETL using SSIS Allan Mitchell SQL Server MVP www.Konesans.com

  2. Why am I here • Worked with DTS since the beginning • Worked onsite with SSIS before RTM • Co-Author on Wrox Professional SSIS Book • Run • www.SQLDTS.com and • www.SQLIS.com • I am not Microsoft.

  3. Agenda/Content Slide • What is ETL and why would I use it? • Microsoft ETL through the Years • Data Transformation Services • SQL Server Integration Services • What makes Microsoft ETL a good choice • Demos

  4. What is ETL? • Extraction, Transformation and Load • Moving data from A  B • Transform, conform data • Most often used in Data Warehouse loads • Also used for • Data Synchronisation • Data Replication

  5. Traditional ETL Architecture Traditional warehouse loading ELT In this traditional scenario, the integration process simply conforms data and loads the database server The database performs aggregations, sorting and other operations… …but has to contend with competing demands for resources from user queries This solution does not scale to very large volumes of data and multiple, complex aggregations

  6. Rethinking ETL Architecture Leveraging SQL Server Integration Services

  7. Is it important? • Getting ETL right is very Important • 70% of a BI project is about the ETL • Why? • No matter how good the cube guy, the reporter. • Bad data and they are dead in the water

  8. Microsoft ETL through the years • SQL Server 7 • Introduced Data Transformation Services • Drag and Drop paradigm • Easy to use • Freely redistributable • Quick • Forgiving

  9. Microsoft ETL through the years • SQL Server 2000 • Enhancements to DTS • Mainly engine based changes • Some things however were painful.

  10. Microsoft ETL through the years • SQL Server 2005 • Microsoft listened to years of input • Complete rewrite • Separates control flow and data flow logic • Containers • Looping • Transactions boundaries • Restart Points • Easy to make environment agnostic

  11. Microsoft ETL through the years • SQL Server 2008 • Enhances SSIS from SQL Server 2005 • Engine multi threading • Persisted lookups • Better feedback from the engine

  12. Demos

  13. Contact • eMail • Allan.mitchell@konesans.com • Live Meetings • www.SQLServerFAQ.com • Websites • www.SQLIS.com • www.SQLDTS.com

More Related