SQL Server 2000 DTS Part 7 – DTS Designer Tasks: the ActiveX Script task

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:DataSourceFile.txt). This can be accomplished with the
following script:

Function Main()
	sFile = "C:DataSourceFile.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:Logsdts.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
the notation DTSGlobalVariables("gVarLogFile").Value.
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
entirely arbitrary):

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:Logsdts.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:Logsdts.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.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles