Expert SQL Server™ 2005 Integration Services

Chapter 2. Extending Scripts in SSIS

Whether applying SSIS toward warehouse ETL or data integration, or toward more complicated DBA tasks, the programming extendibility can be a useful and powerful tool to accomplish workflow tasks in the control flow, or data-centric operations in the data flow, that are not easily handled by out-of-the-box components. You can extend SSIS in this way by using a couple of different supported approaches:

• Through Custom Tasks, Components, and Enumerators, you are able to compile and register native or managed code, as well as extend the product with reusable components just like the built-in components found in the toolbox.

• By using the Script Task or the Script Component, you can write code to accomplish things that are better suited to scripting than what can be accomplished through other components.

Both of these topics are discussed in the Professional SQL Server 2005 Integration Services book (Wiley Publishing, 2006). In that book, Chapter 14 discusses writing a custom task or custom component, and Chapter 15 shows how to write a user interface for your component. Scripting is also covered in Chapter 7 of that book, presenting the basics of the Script Task and the Script Component. All those chapters are commendable. It is beyond the scope of this chapter to cover those topics again at that level. However, we felt that more could be said and exemplified about extending your scripting—taking full advantage of scripting in SSIS when it is appropriate. ...