Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using FileSystemObject with DTS

By Brian Knight,

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

End If

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.

Total article views: 8136 | Views in the last 30 days: 9
Related Articles

global variables

global variables


Global variable

Assign value to global variable


Pass inner global variable from the parent pkg

How to pass inner global variable from the parent pkg using Active Script or Dynamic Properties?


declaring global variables

declaring global variables


Using DTS Global Variables

Global variables are a little known solution in Data Transformation Services (DTS). Often packages ...

sql server 7