Chapter 2: Extending Scripts in SSIS

Overview

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.

What is meant here is that there may be specific times when you might want to customize or extend that functionality beyond basic scripting practices. For example, SSIS runs within the .NET Framework, and, therefore, you can use the base .NET libraries in the Global Access Cache (GAC), or you can build your own custom libraries to handle the custom logic that you need. This chapter shows you how to leverage the power of SSIS by explaining how to extend the Script Tasks and the Script Components, including the following:

• Working with package variables

• Referencing custom libraries

• Updating connections in the Script Task

• Raising error events

• Encryption data in the Script Component

• Profiling data in the data flow