1 / 20

Microsoft SQL Server Integration Service (Expert Level Course) Version 1.0

Microsoft SQL Server Integration Service (Expert Level Course) Version 1.0. What will we cover?. SSIS Enhancements SSIS Pros and Cons Advanced Scripting Optimisation for Scalability Performance Monitoring Interoperability (Excel/Oracle/Linux). SSIS Enhancements.

sana
Download Presentation

Microsoft SQL Server Integration Service (Expert Level Course) Version 1.0

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. Microsoft SQL Server Integration Service (Expert Level Course)Version 1.0

  2. What will we cover? • SSIS Enhancements • SSIS Pros and Cons • Advanced Scripting • Optimisation for Scalability • Performance Monitoring • Interoperability (Excel/Oracle/Linux)

  3. SSIS Enhancements • Development Enhancement • SSIS package designer (Business Intelligence Development Studio) • Integrated with Team Foundation Server. • Separation of Control Flow from Data Flow • Event Handler • Debugging features • Building Packages Programmatically • Microsoft.SqlServer.Dts.Pipeline.WrapperNamespace • Management Enhancement • New Package Storage and management tools (SQL Server Management Studio) • New monitoring and troubleshooting tools • New deployment options • Enhanced Security Features

  4. Basic knowledge of how to build SSIS data flows Familiarity with scripting Helpful Experience Level 300

  5. Architecture

  6. Data Flow Task – New Paradigm SQL/DTSDisk Based Approach Source Staging Prep DWH Extract Transform Load SSIS RAM Based Approach Source DWH Source-Transform-Destination (Dataflow)

  7. Dataflow v SQL – Pros and Cons RAM v Disk Argument Data Flow is fantastic for: workflow, error handling, lookups, calculations, readability, instrumentation interoperability and inserts. Consider SQL for bulk updates, deletes Consider bcp, bulk insert or select into for simple imports Consider development time – t-sql can be faster to develop

  8. SQL Server Integration Services (SSIS) DEMO • Data Flow Dive • OLEDB Source/Destination • Lookups v SQL Joins v Merge Joins • Working with Excel • Control Flow with Scripting intro

  9. Any dot.net compatible language Three types: Source Transformation Destination Careful of performance! • Script component • Key component for implementing custom scenarios

  10. Scripting Transform PreExecute, ProcessInputRow and PostExecute events Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' ' Add your dot.net code here to perform row by row or column by column operation. ' ARE YOU NUTS !! If Row.MyField_IsNull Then 'Process End If End Sub Public Overrides Sub PreExecute() MyBase.PreExecute() 'Add code here to perform tasks before row processing. # ' Eg Prepare a stored Proc End Sub Public Overrides Sub PostExecute() 'Clean up objects. Eg That stored proc you prepared earlier MyBase.PostExecute() End Sub End Class

  11. SQL Server Integration Services (SSIS) DEMO • Advanced Scripting • Deeper Look at Project Real Fact Handling • Early Arriving Facts / Inferred Dim Problem • Off topic look at joys of table partitioning ;-)

  12. Optimising for Scalability Tips Row Transformations aka asynchronous transformations=Good Blocking Transformations (eg Aggregate, sort) = bad Partially Blocking aka synchronous transformations =sometimes ugly On VLDB avoid using: Row by row processing, recordsets, scripting, data object variables, import column, SCD, memory restricted lookups. Use Parallelism (example next slide) If SQL is your source, use it for aggregating, casting, basic calcs and maybe renaming SQL UDF's give you a performance hit but re-usability payoff may be worthwhile. Don’t go overboard on packages: validation, dependencies and complexity will hurt. Use OLEDB Destination with batch size of 10k Stage any large updates or deletes (over 10k records) Don’t bother messing with MaxBufferSize. 10k is the magic number. Use a 64-bit server with 8 cores and 16+GB ram ;-

  13. Parallelism Example

  14. SSIS Performance Monitoring Use WMI for resource monitoring Use MOM for enterprise stuff Use SQL Logging for everything else..sysdtslog90

  15. SQL Server Integration Services (SSIS) DEMO • SSIS Performance Monitoring • sysdtslog90 • analysis in sql server • Custom reporting with SSRS

  16. SSIS Interoperability Data Sources for OLEDB, Excel, Flatfile, SSAS. ADO.Net Dot.net for extensibility and Legacy API Avoid using SSIS to insert into oracle/linux

  17. Oracle Tips Slow in, Fast out ! Use OLEDB driver Restrict oracle user to relevant schema

  18. SQL Server Integration Services (SSIS) DEMO • SSIS Interoperability • Oracle Delta Management

  19. Book References

  20. Thank You Q & A

More Related