SSIS Custom Components Dave Ballantyne

SSIS Custom Components Dave Ballantyne [email protected] @davebally

Why ? Provide new functionality not provided as standard

Why ? Reusability o o o o o Component is a DLL Single code base Can be used multiple times in a single project Can be shared across multiple projects Easy to test Component version Performance o Faster than scripting Well documented o Though not a how-to guide

Types Of Component Data Connections Log Providers For Each Loops Control Flow Tasks Data Flow Pipeline Component Custom User Interface

Pipeline Component Types Sources Transforms Destinations

Design/Run Time Design Time o o o o Work done in BIDS attachments / detachments Validation Column usage Run Time o Metadata interrogation o DTEXEC o Flow of data

Demo 1 Reuse and Performance

Performance Comparison Custom Script 100,000 500,000 1,000,000 5,000,000 10,000,000 20,000,000 31,999,680 261 900 1,667 7,867 16,375 32,852 51,426 684 2,069 3,949 19,214 38,690 76,755 123,243 100% 90% 80% 70% 60% Script Custom 50% 40% 30% 20% 10% 0% 100000 500000 1000000 5000000 10000000 20000000 31999680

Requirements Visual Studio – BIDS is not enough Or Visual Basic / C# Express Client Tools SDK

Starting Out Target Framework 3.5 (Advanced compile options) Sign the assembley Add References(Program file(x86)/ SqlServer /100/sdk/Assemblies) o o o o Microsoft.SqlServer.DTSPipelineWrap Microsoft.SqlServer.DTSRuntimeWrap Microsoft.SqlServer.ManagedDTS Microsoft.SqlServer.PipeLine Host

Class Creation Inherits PipelineComponent Uses attribute DtsPipelineComponent

Post Build Copy DLL to “C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents” Register to Global Assembley Cache using GACUTIL Must Restart BIDS For first use “Choose Items”,”SSIS Data Flow Components” ,tick Component

MetaData IDTSComponentMetaData100 PipelineComponent.ComponentMetaData Describes the Component to the engine Inputs, Outputs Custom data held within IDTSCustomProperty100 o Most level s of object

MetaData Inputs – IDTSInput100 o Exposed via InputCollection member in MetaData o One instance for each attached input o Contains virtual column collection Accessed with GetVirtualInput() member View of the IDTSOutput100 of the Upstream component IDTSVirtualInputColumn100 o Input Column Collection Accessed with InputColumnCollection Those that are used in the component IDTSInputColumn100 o SetUsageType used to add the virtual column to the input column

MetaData Outputs – IDTSOutput100 o Exposed via OutputCollection member in MetaData o One class for each output o output Column Collection Accessed with OutputColumnCollection IDTSOutputColumn100 Dispositions – Errors o Set IsErrorOut on IDTSOutput100

Icons Size 16*16 For ToolBox 32*32 For Design Surface Order of “IconResources” is important Build action must be “Embedded Resource”

Errors and warnings FireError o At design or run time

Errors and warnings FireWarning

Design Time Methods Methods o ProvideComponentProperties Define initial metadata of component o Validate Tests the metadata is correct o ReinitializeMetaData Fix the metadata

Debug

Demo 2 Build a simple component

Run-Time Processing Pre-Execute PrimeOutput ProcessInput PostExecute

PreExecute Setup the runtime objects Interrogate the Metadata and buffer manager Find the colindex(s) in buffers based on metadata o BufferManager.FindColumnByLineageID(InputId,InputCol.LineageId) PrimeOutput

Process Input Loop on buffer.NextRow If buffer.EndOfRowset is true set outputBuffer.SetEndOfRowset() MetaData functions are not optimized for performance.

PipelineBuffer Used for both input and output buffer Get DataType and Set DataType o SetString / GetString o SetInt32 / GetInt32 AddRow o Insert and move to new row SetEndOfRowset o After final row has been poplulated

Sync Or Async ? Sync o Add columns to existing data flow o SynchronousInputID of output ID of input Async o Create new data flow buffer o SynchronousInputID 0

Demo 3 RunTime execution

User Interface

User Interface

User Interface A Class that implements IDtsComponentUI Registered to the component class with UITypeName PublicKeyToken is found with GACUTIL

User Interface

User Interface Demo 4 o User interface o UI Code Step Through

Conclusion Like SSIS , large learning curve Reusability Potentially Faster ? .Net skills are required

SSIS Custom Components Dave Ballantyne [email protected] @davebally