1 / 19

Integration Services in SQL Server 2008

Integration Services in SQL Server 2008. Allan Mitchell – SQLBits – Oct 2007. Introduction. New threading architecture Pipeline limiter (not really new!) New Tasks/Components/Enumerators C# Change Control routines for data extraction Q and A. Who am I?. SQL Server MVP

vito
Download Presentation

Integration Services in SQL Server 2008

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. Integration Services in SQL Server 2008 Allan Mitchell – SQLBits – Oct 2007

  2. Introduction • New threading architecture • Pipeline limiter (not really new!) • New Tasks/Components/Enumerators • C# • Change Control routines for data extraction • Q and A

  3. Who am I? • SQL Server MVP • Co author on Wrox book on SSIS • www.SQLDTS.com, www.SQLIS.com • Consultancy company – Konesans (www.konesans.com)

  4. What I will not cover • Finding SSIS • Creating packages • General package design

  5. Threading • How is it now? • Some processes use single threads • Why is this not brilliant? • Sync Outputs with a blocking transform on one of the outputs kills performance • What is coming? • Threading model changed to allow components to go out and try to get their own thread • Why will this help? • Takes advantage of today’s bigger, faster boxes

  6. DEMO

  7. Pipeline Limiter • Why would they want to slow me down? • Fill buffers and run out of memory • I see it now in SQL Server 2005? Really? • You see it but SSIS keeps quiet

  8. DEMO

  9. New Tasks and Components (Data Collector Specific) • Tasks • Update Row Counts • Reset Row Counts • Trace Start Script • Cache Window Maintenance • Components • TxPerfCounters

  10. New Enumerator (s) • ForEach Database Enumerator • Uses OLEDB for connection • Configure what DBs you enumerate (see next slide) • Rubbish UI experience

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

  12. DEMO

  13. C# ! • Script Component • Script Task • Custom component development has always been available in C#

  14. DEMO (Quick Look)

  15. Using New DB Change Control • Billed as an ETL feature • We’ll use it if it’s there • What is it? • How do I use it then?

  16. What and Why • Increased efficiency of ETL Process • Incremental Extractions • Identify changed rows and columns • Identify operation on data, all changes or net changes • Log Based (Transactional Replication LR) • Lightweight

  17. Terminology • Capture Instance – Base Object (Max 2 per object) • Capture Process – Reading of the log and placing the rows into the change tables

  18. DEMO

  19. Questions? Email: allan.mitchell@konesans.com

More Related