If you have been following our series of articles discussing SQL Server DTS technology, you probably have noticed frequent references to ActiveX-based scripting. While scripting is not very popular among database administrators, its potentials are worth exploring, especially since familiarity with its concepts is required to fully understand the remaining types of tasks we will be covering. We will start this coverage with the ActiveX Script task, which, as you might expect, utilizes scripting functionality most extensively.
The purpose of an ActiveX Script task is to execute a VBScript (which will be our language of choice) or JScript code (other scripting languages can also be used as long as they are installed on a system where packages are created and the one where they are launched) as part of DTS packages. To create it, simply select the ActiveX Script Task option from the Task menu in the DTS Designer window (or use the appropriate icon from the Task toolbar on the left hand side of the window). In the ActiveX Script task Properties dialog box, you can select the scripting language. This, in turn, determines the listing of functions specific to this language, displayed in the Functions box. The main text box on the right hand side is intended for your code. Initially, it contains the definition of the Function Main, created automatically for you and consisting of the following lines:
'********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() Main = DTSTaskExecResult_Success End Function
This basic content does not really provide any meaningful functionality but serves simply as a starting point for developing your own scripts. You can recreate it at any point by clicking on the Auto Gen. button in the task Properties dialog box (although you most likely want to avoid this button afterwards, as it will erase any custom code you created). Since a code can contain multiple functions, you specify which one should be executed by filling out the Entry function text box (by default, this is Main). At any point, you can verify syntactical correctness of your code by clicking on the Parse button.
Our assumption is that you are familiar with the principles of scripting. If this is not the case, you can download introductory information in the form of compiled HTML help files (.CHM) from the Microsoft Web site. However, even if you have written some scripts before, it might take a bit of effort to get used to the DTS specific elements referenced in ActiveX script task code. Fortunately, such elements are easily identifiable since they always start with the "DTS" prefix and their name usually clearly indicates their purpose. For example, the auto-generated code we listed above includes DTSTaskExecResult_Success entry. This name designates a constant indicating successful completion of the task. Conversely, DTSTaskExecResult_Failure is a constant indicating a task failure. Within your script, you should detect its outcome and report it by assigning appropriate constant to Main. For example, let's assume that we want to confirm the presence of a specific file (C:\Data\SourceFile.txt). This can be accomplished with the following script:
Function Main() sFile = "C:\Data\SourceFile.txt" Set oFSO = CreateObject("Scripting.FileSystemObject") If oFSO.FileExists(sFile) Then MsgBox "The " & sFile & " exists" Main = DTSTaskExecResult_Success Else MsgBox "The " & sFile & " does not exists" Main = DTSTaskExecResult_Failure End If End Function
The script uses the Scripting.FileSystemObject object model that contains methods and properties allowing operations on files and folders. We use the FileExist method to determine whether the target file exists and, if so, we display confirmation in a dialog box and set the value returned from the Main function to DTSTaskExecResult_Success. Otherwise, we alter the displayed message and returned value to indicate failure. Note that, in general, it is not a good idea to display message boxes during task execution, since this prevents successful completion of scheduled packages, which need to be able to run unattended. We included it here strictly for demonstration purposes.
With the Scripting.FileSystemObject library, you can also delete a DTS log file, which provides an alternative to the solution included in our previous article, which used ExcecuteProcess task. In the most basic case, ActiveX script would take the following form:
Function Main() sFile = "C:\Logs\dts.log" Set oFSO = CreateObject("Scripting.FileSystemObject") If oFSO.FileExists(sFile) Then oFSO.DeleteFile sFile, TRUE End If Main = DTSTaskExecResult_Success End Function
The DeleteFile method of the Scripting.FileSystemObject object deletes a file, which name you specified. The optional second argument of this method, when set to TRUE, forces the operation even if the file has the read-only attribute set. Now let's take a look how we can make this script more flexible by incorporating global variables into it.
First, it is important to realize that in order to work efficiently with DTS scripts, you need to be familiar with the DTS Object model. This terms describes a collection of objects (each with its own properties and methods), representing various DTS elements. Within this model you will find a Package object (more specifically, Package2, which is SQL 2000 version of the Package object introduced in SQL 7.0 DTS), which contains such sub-object as Connections, Tasks (there are separate objects for different types of tasks), Steps, or GlobalVariables. The diagram representing the object model is published on the MSDN web site. While initially this diagram might be confusing, it should become clearer after analyzing some sample scripts. You should also keep in mind that, when referencing objects listed in the diagram from ActiveX scripts, you should always include the "DTS" prefix (so, for example, GlobalVariables entry on the diagram should be referred to as DTSGlobalVariables instead).
DTSGlobalVariables designates a collection of all global variables in a
package. You can access it directly from an ActiveX script. For example, the value
of the global variable named gVarLogFile can be extracted in the script using
You can also create a new global variable within your script by executing a
command in the following format (obviously, the variable name and value are
Set DTSGlobalVariables("gVarJustCreated").Value = "Hello World"
Let's try the first approach in our script. We will create a global variable called gVarLogFile manually in our package. As you know by now, this is done from the Global Variables tab of the package Properties dialog box. Name it gVarLogFile and assign it the value C:\Logs\dts.log. Next, modify the code within the ActiveX task so it looks as follows:
Function Main() sFile = DTSGlobalVariables("gVarLogFile").Value Set oFSO = CreateObject("Scripting.FileSystemObject") If oFSO.FileExists(sFile) Then oFSO.DeleteFile sFile, TRUE End If Main = DTSTaskExecResult_Success End Function
Save the package with a distinctive name (e.g. ActiveX Test) and execute it. As before, this should result in successful completion and deletion of C:\Logs\dts.log file.
As you can imagine, we barely touched on functionality of ActiveX scripting. Capabilities of ActiveX task (and DTS based scripting or programming in general) are practically unlimited, since they provide access to not only DTS components (via DTS object model) but also other object models (e.g. ADO, WSH, CDO, etc.). We will be taking advantage of these features in our future articles of this series.
You should keep in mind two additional factors when working with ActiveX Script tasks:
- a script operates on the same system where a package that contains it is executed. This has negative impact on completion time if you tend to launch your packages from SQL Server Enterprise Manager running on your workstation, rather than directly from a server. To prevent this, schedule your packages as SQL Server Agent jobs or launch them directly from your SQL Server console.
- avoid using ActiveX Script tasks to manipulate data on a row-by-row basis, especially on larger datasets, since such methods tend to consume a lot of time and processing power. Instead, manipulate such data directly on the SQL Server (either before or after package runs, depending on data source and destination).
We will continue with our coverage of DTS tasks in the next article of this series.