IntroductionSSIS has a rich set of Task and Transformation but still developer may have a requirement or for fun want to create own custom task. SSIS allow 5 Types of custom objects - Custom tasks.
- Custom connection managers.
- Custom log providers.
- Custom enumerators.
- Custom data flow components.
A. To create a custom object you will need to Microsoft.DataTransformationServices.Controls to references. This doesnt show up in .Net tab so you can browse to C:\Windows\Assembly to add B. Its usually a good design practice to Separate out the UI code and Runtime Code so that only runtime code is loaded during execution of the package. C. UI: To develop a UI one would need inherit from propere base class according to the Custom Object. | Custom object | Base class |
|---|
Task | | Connection manager | IDtsConnectionManagerUI | Log provider | IDtsLogProviderUI | Enumerator | ForEachEnumeratorUI | Data flow component | IDtsComponentUI |
D. Runtime code : Inherite base class according to the table E. Apply the appropriate attribute to your new class. F. Override the methods of the base class as required and write code for the custom functionality of your object. | Custom object | Base class | Attribute | Important methods |
|---|
Task | Task | DtsTaskAttribute | Execute | Connection manager | ConnectionManagerBase | DtsConnectionAttribute | AcquireConnection,ReleaseConnection | Log provider | LogProviderBase | DtsLogProviderAttribute | OpenLog,Log,CloseLog | Enumerator | ForEachEnumerator | DtsForEachEnumeratorAttribute | GetEnumerator | Data flow component | PipelineComponent | DtsPipelineComponentAttribute | ProvideComponentProperties,PrimeOutput,ProcessInput |
G. After Creating class, you would need to create the dll and builing it and dll has to be signed with strong key H. Install Custom object dll to GAC and copy dll to SQL SERVER DTS objects fodler for example if custome task has to be copied to \\Program Files\Microsoft SQL Server\100\DTS\Tasks You can see and download an example of custom task from here. http://sqlserversolutions.blogspot.com/2010/05/create-custom-task-in-ssis.html i hope this is help to you all. |