Last week we visited how to use global variables in DTS.

This week we'll read a file from a directory and create a dialog box from the file's name.

This exercise is especially useful when you're reading a file from a directory to dyanmically set properties of a task or connection.

First create a directory on your C drive called data. Place two empty text files in those directories called data1.txt and data2.txt.

Next, create two global variables as shown below. You can create a global variable by clicking on Properties in the Package menu.

Now that your global variables are created, you can begin your ActiveX script task. Click on the ActiveX script tasks under the tasks menu.

Below is the script in its entirety.

Let's go ahead and take this script apart. This first step is to declare your variables and create your objets.

Since we are using the FileSystemObject, we will create the object and use the variable

oFS to represent the object.

Dim oFS

Dim colFile

Dim oFolder

Dim oFile

Set oFS = CreateObject("Scripting.FileSystemObject")

Since we are not making any changes to the package or its global variables, we will

not need the following line. However, I include this line for future expansion of the script:

Set oPkg = DTSGlobalVariables.Parent

We will now need a few variables to be set before we can continue. We will use

the global variables that you declared earlier in this article. This is the main benefit of using global variables.

If you had 25 scripts all running in conjuction, without the use of global variables, you would have to change 25 scripts

for a minor change such as moving the location of the files. With the use of global variables, you can just set the global variable in one

location and allow it to be called from multiple scripts. A big time saver!

The GetFolder method will set which folder we will look in to retrieve the files.

Now that you have the folder set, the oFolder.Files method will list all the files in that folder.

Set oFolder = oFS.GetFolder(DTSGlobalVariables("gsUploadDirectory").value)

Set colFile = oFolder.Files

You need to now loop through all the files in that directory that end with the extention .TXT and

perform an action on them. That is done through a For..Each loop. In the below code

we are looping through our files in the colFile array and loading a dialog box with the file name when found.

For Each oFile in colFile

If UCase(Right(oFile, 4)) = DTSGlobalVariables("gsExtention").value Then

Dialog=Msgbox(oFile.Name)

End If

Next


Go ahead and execute the package. If all goes well, you should see the following:

Next week we will go into setting connections dynamically.

A great book if you are interested in further researching VBScript is

VBScript Programmer's Reference.

It gives you a background in the fundamentals and gets into the details of how to program in VBScript.