Platinum Gold Silver Learn Enjoy Put your phone

Platinum Gold Silver Learn & Enjoy [Put your phone on Vibrate!] www. sqlbits. com Group BY: [Food and Drink at Reading Bowl, see you there!] Feedback Forms: [Voucher for £ 30 book on return of Form] 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

Extending SSIS with Custom Tasks Darren Green Konesans Ltd

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

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

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

References • Microsoft. SQLServer. Managed. DTS – Microsoft. Sql. Server. Dts. Runtime • Microsoft. SQLServer. DTSRuntime. Wrap – Microsoft. Sql. Server. Dts. Runtime. Wrapper • Microsoft. Sql. Server. Pipeline. Host – Microsoft. Sql. Server. Dts. Pipeline • Microsoft. Sql. Server. DTSPipeline. Wrap – Microsoft. Sql. Server. Dts. Pipeline. Wrapper • Microsoft. Sql. Server. Dts. Design – Microsoft. Sql. Server. Dts. Runtime. Design

Custom Objects - Template [Object. Attribute(Information)] public class My. Custom. Object : Base. Class { public override void Base. Method() { // Custom code } }

Classes & Attributes Base Class Attribute Connection. Manager. Base Dts. Connection. Attribute Pipeline. Component Dts. Pipeline. Component. Attribute For. Each. Enumerator Dts. For. Each. Enumerator. Attribute Log. Provider. Base Dts. Log. Provider. Attribute Task Dts. Task. Attribute

Demo Simple Task

Tasks • Methods – Initialize. Task – 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

More Task Features • Custom Events • Custom Log Entries • Breakpoints (IDTSBreakpoint. Site)

Create a Task User Interface • Create UI class (Implement IDts. Task. UI) • Create UI form • Set task’s UIType. Name attribute

Demo Simple Task User Interface

Advanced Task User Interfaces • Microsoft. Data. Transformation. Services. Controls. dll • DTSBase. Task. UI Base Form Class – DTSTask. UIHost Control, paged control – Class per page, implement IDTSTask. UIView

UI Classes & Attributes Interface / Class Notes IDts. Connection. Manager. UI Use Win Form IDts. Component. UI Use Win Form CManaged. Component. Wrapper For. Each. Enumerator. UI User. Control IDts. Log. Provider. UI Not Supported IDts. Task. UI Use Win Form

Debugging • Designer – Design time – Attach to devenv. exe • Designer – Runtime – Attach to DTSDebug. Host. exe • Component Project – Runtime – Start external program C: Program. . . DTExec. exe /F "C: . . . Test. Package. dtsx”

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 "$(Target. Path)“ C: Program. . . – gacutil. exe" /if "$(Target. Path)“ • Ensure persistence complete

Installation Locations • Designer Enumeration Folder – Designer location only (x 86 Only) C: Program FilesMicrosoft SQL Server90DTS<Object> Connections Log. Providers For. Each. Enumerators Pipeline. Components Tasks • Global Assembly Cache – Runtime loading by execution host

Install Platform Target 32 -bit (x 86) Location Program Files GAC • • x 86 x 64 IA 64 MSIL Design Run Install in Program Files for Designer Install in GAC for run-time Support for x 86 specific targeted assemblies Support for Any CPU / MSIL

Install Platform Target 64 -bit (x 64) Location Program Files (x 86) GAC • • • x 86 x 64 IA 64 Design Run MSIL Design Run Support for x 86 only targeted assemblies Support for x 64 only targeted assemblies Support for Any CPU / MSIL Designer is x 86/MSIL only Can side by side target assemblies Run

Install Platform Target Itanium (IA 64) Location x 86 GAC • Support for IA 64 • Support for Any CPU / MSIL • No designer support x 64 IA 64 MSIL Run

64 -bit Tools Only Location x 86 x 64 IA 64 MSIL Program Files (x 86) GAC Run • No x 86 file requirement • No x 86 DTSPath registry key • MSI registry search is 32 -bit • No key, no tools, no designer, no file required!

Install Tips • Use MSI builder of choice, e. g. VS, Wi. X • Registry Search for DTS folder location HKLMSOFTWAREMicrosoftMSDTSSetupDTSPath C: Program FilesMicrosoft SQL Server90DTS • Allow tools only installs, don’t require 32 -bit key • User Interfaces & Support Assemblies? – GAC Only • Support Files? – Use special folders, Common. Application. Data • Minimal UI, no options required

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

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/Wiley. CDA/Wrox. Title/product. Cd -0764584359. html • Microsoft SQL Server 2005 Integration Services – SAMS (Ch 24 – 25 - Samples) http: //www. samspublishing. com/bookstore/product. a sp? isbn=0672327813

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

Platinum Gold Silver www. SQLBits. com [Conference Web site] www. sqlbits. com www. SQLBlog. Casts. com [Becoming the premier Blogging site for SQL professionals] www. SQLServer. FAQ. com [UK SQL Server Community Website] UK SQL Bloggers cwebbbi. spaces. live. com sqlblogcasts. com/blogs/simons sqlblogcasts. com/blogs/tonyrogerson Feedback Forms!!