1 / 19

Advanced Topics Using Microsoft SQL Server 2005 Integration Services

Explore loading SSAS partitions directly, using SSIS as a data source, data mining, and performance tuning in SQL Server 2005 Integration Services. Attendees will gain insights on optimization strategies and practical demos.

robertpoole
Download Presentation

Advanced Topics Using Microsoft SQL Server 2005 Integration Services

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. Advanced Topics Using Microsoft SQL Server 2005 Integration Services Allan Mitchell – SQLBits – Oct 2007

  2. Introduction • Loading SSAS partitions directly • Using an SSIS Package as a data source • Data Mining in your Packages • Understanding Performance • 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. Loading SSAS Directly • Dimension And Partition Processing • Do not need to load the underlying DB • Quicker ROI • Don’t fully process the Partition/Dimension outside of the ETL process !

  6. DEMO

  7. Using SSIS as a data source • SSRS • Complex logic (Microsoft.SqlServer.Dts.DtsClient) • Windows App • Populate a web page perhaps • Custom Component • Take the output of one package and use it in another

  8. DEMO

  9. Data Mining in SSIS • Transforms • Data Mining Query • Insurance Company perhaps • Term Extraction • Textual Extraction (DT_WSTR or DT_NTEXT) • Term lookup • Textual Lookup (DT_WSTR or DT_NTEXT) • Destinations • Data Mining Model training

  10. DEMO

  11. Performance Tuning • Data Correlation • Use a merge Join over lookup – why? • Remember sorted inputs – Do on source preferably. • Avoid delay between 2 sources hitting.

  12. Performance Tuning • Buffer Strategy • DefaultMaxBufferRows (10000) • DefaultMaxBufferSize (10 MB) • MaxBufferSize and MinBufferSize (100MB and 64KB) • Avoid many small buffers. • Turn on logging of BufferSizeTuning event

  13. Parallel Load (Try 1)

  14. Parallel Load (Try 2)

  15. Parallel Load (Try 3)

  16. The Raw File • Only SSIS can read and write • Only Adapter that does not use a Connection Manager • High Speed • Source and Destination • No datatype conversions required • Eases back pressure

  17. Demo • The Raw File and How To Read

  18. Access Modes and OLEDB Source Adapter • Do not use Table or View as Access Mode • Use SQL Command instead • Openrowset (Overhead + bad exec plan) • Sp_prepare (much better) • (MS say 17* improvement)

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

More Related