This chapter discusses SSIS customization-the built-in capability of the SSIS platform that allows you to extend the natively provided programmatic elements. In addition to the system-provided tasks and components, including the script task and the script component, the SSIS programming model allows you to implement your own programmatic logic by designing your own control flow tasks (custom tasks) or your own data flow components (custom components).
Typically, a custom task would be needed when none of the system-provided tasks facilitate the specific operation that you need to implement in your SSIS solution; for instance, the built-in File Transfer Protocol (FTP) task does not support Secure FTP, so if you need to access remote file locations using the Secure File Transfer Protocol (SSH FTP), you need to design a custom task.
The most frequent uses of the custom component are custom transformations that either provide operations that are not provided by the built-in components...
To design a custom control flow task, you create a DOT.NET assembly based on the Class Library Visual Studio template; the task's definition must be placed in a class derived from the Task base class, of the Microsoft.SqlServer.Dts.Runtime namespace (located in the Microsoft.SqlServer.ManagedDTS.dll assembly). This class also needs to implement the DtsTaskAttribute that is used to identify the class as an SSIS task and provide the elementary properties used when implementing the custom task in SSIS control flows.
Optionally, you can provide a custom graphical user interface for the custom task, which will be used as the task editor when the task is configured during SSIS package development. If a custom editor is not provided, the custom task can be edited by using the built-in advanced editor.
The task base class provides two methods that you need to override, and in them provide your custom code to:
- Validate the configuration of the task. This method is called automatically...
To design a Custom Data Flow Component, you need to create a .NET assembly based on the Class Library Visual Studio template; the class with the component's definition must be derived from the PipelineComponent base class of the Microsoft.SqlServer.Dts.Pipeline namespace. The class also needs to implement the DtsPipelineComponentAttribute that allows the class to be identified as an SSIS component and to provide the essential properties of the component used in the development of SSIS data flows.
If you want to simplify the configuration of the component, you can provide a custom graphical user interface; otherwise, the Advanced Transformation Editor will be used to configure the component during SSIS package development.
The component also needs access to the interfaces and classes of the Microsoft.SqlServer.Dts.Pipeline.Wrapper namespace. Depending on the functionalities provided by the component, additional references might be needed to the Microsoft.SqlServer...
Over time you might need to make changes to a custom component, for instance because you needed to optimize or refactor the code, implement new features, or replace the external assemblies. As long as the interface of the component (its custom properties and its inputs and outputs) remain unchanged in a later version, you simply deploy the new version to the destination environments, register it in the GAC, and the new version of the component will be used the next time the package is executed or edited in SSDT. You do not even have to modify its version number.
However, if the change affects the components interface - for instance, if you need to add, remove, or modify any of its custom properties - you need to make the component upgradable. This upgrade is performed automatically - at design time or at run time - by invoking a special design-time method of the Microsoft.SqlServer.Dts.Pipeline. PipelineComponent base class, namely the PerformUpgrade() method...
© 2017 Packt Publishing Limited All Rights ReservedAuthors (6)
