Innovating Data Integration with SQL Server 2005 Integration Services

1
Innovating Data Integration with SQL Server 2005
Integration Services

• Davide Mauri Solid Quality Mentors
• dmauri_at_solidq.com

2
EXEC sp_help Davide Mauri

• MCDBA, MCAD, MCT
• Microsoft SQL Server MVP
• President of UGISS (Italian SQL Server UG)
• Mentor _at_ Solid Quality Mentors
• Italian Subsidiary

3
Agenda

• ETL with Integration Services
• Extending SSIS
• Scripting
• Custom Objects
• SSIS as a Framework
• Using SSIS in Custom Application
• Custom SSIS Management
• Q A

4
Traditional ETL Pain ?
5
ETL with Integration Services
Cube
6
DEMO

• Standard Integration Services

7
Extending SSIS

• Very simple solution scripting!
• Control Flow Script Task
• Data Flow Script Transformation
• Custom Source / Destination
• Custom Transformation
• Synchronous Asynchronous

8
Extending SSIS

• Script Transform is particularly powerful
• Yet not so difficult to use
• It may easily save the day! ?
• If you need to use a scripted solution more and
  more in other package you should create a Custom
  Transformation
• Not as easy

9
Extending SSIS

• SSIS can be totally extended through custom
  objects
• Connection Managers
• Enumerators
• Tasks
• Transformations
• Events
• Log Providers

10
DEMO

• Extending SSIS Demos

11
SSIS as a Framework

• SSIS can be embedded in custom applications
• Create packages
• Modify packages
• Manage packages
• Call packages and receive results
• Call package and intercept events
• Particularly useful for creating your own
  execution environment

12
SSIS as a Framework

• DTLoggedExec
• Needed a way more detailed and configurable
  logging
• Dont want to have to modify package to have a
  log
• Wrote an application that executes packages and
  intercept every event
• Used the nice Object Model exposed by SSIS
• http//www.codeplex.com/DTLoggedExec

13
DEMO

• SSIS as a Framework

14
Question?Answers! ?

15
THANKS FOR PARTICIPATING!