Chapter 6: Scripting Tasks

 Download CD Content

Overview

After completing this chapter, you will be able to:

• Create, modify, and execute custom behaviors, using the Script Task, Script Component, and ActiveX Script Task.

• Write custom code in the Microsoft Visual Studio for Application (VSA) development environment, using Microsoft Visual Basic .NET.

• Incorporate external Microsoft .NET assemblies and COM into the Script Task and Script Component.

• Implement error-handling code, using TRY…CATCH…FINALLY statements.

• Access SSIS built-in capabilities such as Variables, Events, and Logs from custom code in the Script Task and Script Component.

In the previous chapters, you learned how to create a package by using Control Flow tasks and containers as well as Data Flow components. However, you might discover that Microsoft SQL Server Integration Services (SSIS) built-in items do not satisfy complicated requirements in some cases. For example, how can you get rid of unnecessary lines in a comma-separated-value (CSV) file before you import the file? What if you need to look up employees' information in Active Directory directory services while you are loading data from a flat file?

To support these kinds of complicated requirements, SSIS provides a way to implement custom script code in your package to extend its abilities. SSIS provides three options to implement script code: the Script Task, Script Component, and ActiveX Script Task. In this chapter, you will learn the differences among them and how to implement them in your package. SQL Server Business Intelligence Development Studio (BIDS) provides you with a special development environment to implement script code, called Microsoft Visual Studio for Applications (VSA). Throughout this chapter, you will also learn how to use VSA to implement script code.