1 / 27

Platinum

Platinum. Learn & Enjoy [Put your phone on Vibrate!]. www.sqlbits.com. Group BY: [Food and Drink at Reading Bowl, see you there!]. Gold. Feedback Forms: [Voucher for £30 book on return of Form]. Silver. Lunch Time Sessions: [Idera in Everest, Quest in Memphis, Grok in Chic 1 and 2].

zola
Download Presentation

Platinum

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. Platinum Learn & Enjoy [Put your phone on Vibrate!] www.sqlbits.com Group BY: [Food and Drink at Reading Bowl, see you there!] Gold Feedback Forms: [Voucher for £30 book on return of Form] Silver Lunch Time Sessions: [Idera in Everest, Quest in Memphis, Grok in Chic 1 and 2] Ask The Experts[Sessions need to finish on time, take questions to the ATE area] Extending SSIS with Custom Tasks

  2. Extending SSIS with Custom Tasks Darren Green Konesans Ltd

  3. Agenda • Extending SSIS • Types • Six Steps • Tasks • Debugging • Installation • When and Why?

  4. Types • Task • Pipeline Component • Source • Destination • Transform • Log Provider • For Each Enumerator • Connection Manager

  5. Six Steps • Create project • Add references • Add signing key • Write code • Compile • Install

  6. References • Microsoft.SQLServer.ManagedDTS • Microsoft.SqlServer.Dts.Runtime • Microsoft.SQLServer.DTSRuntimeWrap • Microsoft.SqlServer.Dts.Runtime.Wrapper • Microsoft.SqlServer.PipelineHost • Microsoft.SqlServer.Dts.Pipeline • Microsoft.SqlServer.DTSPipelineWrap • Microsoft.SqlServer.Dts.Pipeline.Wrapper • Microsoft.SqlServer.Dts.Design • Microsoft.SqlServer.Dts.Runtime.Design

  7. Custom Objects - Template [ObjectAttribute(Information)] public class MyCustomObject : BaseClass { public override void BaseMethod() { // Custom code } }

  8. Classes & Attributes

  9. Demo Simple Task

  10. Tasks • Methods • InitializeTask • Validate • Execute • Properties • Use attributes, editors and type converters • Use connection GUID • Write only for security when required • Custom persistence for sensitive data or complex types

  11. More Task Features • Custom Events • Custom Log Entries • Breakpoints (IDTSBreakpointSite)

  12. Create a Task User Interface • Create UI class (Implement IDtsTaskUI) • Create UI form • Set task’s UITypeName attribute

  13. Demo Simple Task User Interface

  14. Advanced Task User Interfaces • Microsoft.DataTransformationServices.Controls Microsoft.DataTransformationServices.Controls.dll • DTSBaseTaskUI Base Form Class • DTSTaskUIHost Control, paged control • Class per page, implement IDTSTaskUIView

  15. UI Classes & Attributes

  16. Debugging • Designer – Design time • Attach to devenv.exe • Designer – Runtime • Attach to DTSDebugHost.exe • Component Project – Runtime • Start external program C:\Program...\DTExec.exe /F "C:\...TestPackage.dtsx”

  17. Debugging Tips • Ensure class and strong name is fixed at start • Must restart BIDS between builds, slow! • Use DTExec for all runtime • Use Post Build Event for install • copy "$(TargetPath)“ C:\Program... • gacutil.exe" /if "$(TargetPath)“ • Ensure persistence complete

  18. Installation Locations • Designer Enumeration Folder • Designer location only (x86 Only) C:\Program Files\Microsoft SQL Server\90\DTS\<Object> \Connections \LogProviders \ForEachEnumerators \PipelineComponents \Tasks • Global Assembly Cache • Runtime loading by execution host

  19. Install Platform Target 32-bit (x86) • Install in Program Files for Designer • Install in GAC for run-time • Support for x86 specific targeted assemblies • Support for Any CPU / MSIL

  20. Install Platform Target 64-bit (x64) • Support for x86 only targeted assemblies • Support for x64 only targeted assemblies • Support for Any CPU / MSIL • Designer is x86/MSIL only • Can side by side target assemblies

  21. Install Platform Target Itanium (IA64) • Support for IA64 • Support for Any CPU / MSIL • No designer support

  22. 64-bit Tools Only • No x86 file requirement • No x86 DTSPath registry key • MSI registry search is 32-bit • No key, no tools, no designer, no file required!

  23. Install Tips • Use MSI builder of choice, e.g. VS, WiX • Registry Search for DTS folder location HKLM\SOFTWARE\Microsoft\MSDTS\Setup\DTSPath C:\Program Files\Microsoft SQL Server\90\DTS\ • Allow tools only installs, don’t require 32-bit key • User Interfaces & Support Assemblies? • GAC Only • Support Files? • Use special folders, CommonApplicationData • Minimal UI, no options required

  24. When to extend • Reuse in multiple packages • Increased Development vs Lower Maintenance • Access legacy resources (COM) • Complex business logic • More advanced code requirements • Powerful IDE • Language choice

  25. Resources - Samples • Microsoft Download Center http://www.microsoft.com/downloads/ Search for “SQL Server SSIS Sample Component” • Professional SQL Server 2005 Integration Services – WROX (Ch 14 – 15 – Samples) http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584359.html • Microsoft SQL Server 2005 Integration Services – SAMS (Ch 24 – 25 - Samples) http://www.samspublishing.com/bookstore/product.asp?isbn=0672327813

  26. Thank You! Darren Green Konesans Ltd darren.green@konesans.com http://www.konesans.com http://www.sqldts.com http://www.sqlis.com

  27. Platinum www.SQLBits.com[Conference Web site] www.sqlbits.com www.SQLBlogCasts.com[Becoming the premier Blogging site for SQL professionals] Gold www.SQLServerFAQ.com[UK SQL Server Community Website] Silver UK SQL Bloggers cwebbbi.spaces.live.comsqlblogcasts.com/blogs/simonssqlblogcasts.com/blogs/tonyrogerson Feedback Forms!!

More Related