1 / 34

SSIS Custom Components

SSIS Custom Components. Dave Ballantyne dave.ballantyne@live.co.uk @ davebally. Why ?. Provide new functionality not provided as standard. Why ?. Reusability Component is a DLL Single code base Can be used multiple times in a single project Can be shared across multiple projects

elda
Download Presentation

SSIS Custom Components

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. SSIS Custom Components Dave Ballantyne dave.ballantyne@live.co.uk @davebally

  2. Why ? • Provide new functionality not provided as standard

  3. Why ? • Reusability • 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 • Faster than scripting • Well documented • Though not a how-to guide

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

  5. Pipeline Component Types • Sources • Transforms • Destinations

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

  7. Demo 1 • Reuse and Performance

  8. Performance Comparison

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

  10. Starting Out • Target Framework 3.5 (Advanced compile options) • Sign the assembley • Add References(Program file(x86)/<SqlServer>/100/sdk/Assemblies) • Microsoft.SqlServer.DTSPipelineWrap • Microsoft.SqlServer.DTSRuntimeWrap • Microsoft.SqlServer.ManagedDTS • Microsoft.SqlServer.PipeLineHost

  11. Class Creation • Inherits PipelineComponent • Uses attribute DtsPipelineComponent

  12. 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

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

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

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

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

  17. Errors and warnings • FireError • At design or run time

  18. Errors and warnings • FireWarning

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

  20. Debug

  21. Demo 2 • Build a simple component

  22. Run-Time Processing • Pre-Execute • PrimeOutput • ProcessInput • PostExecute

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

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

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

  26. Sync Or Async ? • Sync • Add columns to existing data flow • SynchronousInputID of output = ID of input • Async • Create new data flow buffer • SynchronousInputID =0

  27. Demo 3 • RunTime execution

  28. User Interface

  29. User Interface

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

  31. User Interface

  32. User Interface • Demo 4 • User interface • UI Code Step Through

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

  34. SSIS Custom Components Dave Ballantyne dave.ballantyne@live.co.uk @davebally

More Related