Chapter 7: Using ActiveX Script Tasks

Overview

In this chapter, you will learn how to:

• Set global variable values

• Read registry values

• Enable package branching

In previous chapters, you learned how to use Microsoft ActiveX scripts in conjunction with the Data Driven Query task to add functionality to your data movement application. In this chapter, you will learn to use the ActiveX Script task to expand your application s functionality beyond what is available in the built-in tasks. Specifically, you will learn to set global variable values, read registry values, and enable package branching. These are examples of some of the functionality that you can add to packages by using ActiveX Script tasks.

Using the ActiveX Script task, you can access a variety of COM objects, such as the DTS object model, the WSH (Windows Scripting Host) object model, and the FileSystemObject object model, to dynamically configure DTS tasks and packages and control package workflow. Figure 7-1 shows Part 1 and Figure 7-2 shows Part 2 of the DTS object model.

Figure 7-1: Part 1 of the DTS object model

Figure 7-2: Part 2 of the DTS object model

The root-level object of the DTS object model is the Package2 object. (The Microsoft SQL Server 7 version of DTS uses the Package object.) In an ActiveX task, you can access any of the connection objects, tasks, steps, or global variables in a package through the Package2 object. Similarly, you can access any file in the Microsoft Windows file system through the FileSystemObject object, and any key or value in the Windows registry through the WScript object. In this chapter, you will learn how to access the DTS object model and the WSH object model to add additional dynamic configuration capabilities to your data movement application. (For more information on DTS programming, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtsptasks_4yp1.asp?frame=true . For information about the WSH object model, see http://www.microsoft.com/technet/treeview/default.asp?url=/technet/scriptcenter/scrguide/sas_wsh_rjma.asp .)

You can use one of many interpreted scripting languages within an ActiveX Script task. Interpreted languages are not compiled until run time, which means that you can change the code without having to recompile it after each change. However, this flexibility comes at the price of performance ”each ActiveX Script task is compiled before it is executed. As a result, you should use ActiveX Script tasks only for tasks that you cannot perform using one of the built-in tasks. DTS compiles Microsoft Visual Basic Script (VBScript) more efficiently than JavaScript, and it compiles JavaScript more efficiently than PerlScript. Furthermore, Microsoft has fully tested only VBScript and JavaScript with the ActiveX Script task. Although the examples in this book use VBScript, you could write them using any interpreted language. This book assumes that you are familiar with VBScript and does not attempt to explain the details of the VBScript used in the ActiveX Script tasks, although the overall concept of each script is explained. For information about VBScript, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vtoriVBScript.asp or Microsoft Windows 2000 Scripting Guide, published by Microsoft Press ( http://www.microsoft.com/MSPress/books/6417.asp ).

When you incorporate ActiveX scripts to change package properties during execution, the changed objects in the package can be left without a context after the package is executed. When you execute such a package within DTS Designer, make further edits to the package, and then save it, package corruption can result. To avoid this potential problem, you should always save your package before you test its execution and then use the saved package for further edits. Do not use the executed package for further edits. Close and then re- open the package.