Using Data Transformation Services, part 3: Using the DTS Designer Objects (continued) - The FileSystemObject
(Page 5 of 9 )
Since we are dealing with the text files that will be stored on the computer, a way must be provided to access the files. The FileSystemObject is a very flexible object that provides methods and properties for looking deeply into the local folders and files. The FileSystemObject can be accessed by using the CreateObject method as shown here:
set fsys=CreateObject("Scripting.FileSystemObject")
Once created, the methods and properties of the object provides flexible access to the files and folders.
The HelloDTS ActiveX Script task has the following script:
'*********************************************
' Visual Basic ActiveX Script
'*********************************************
Function Main()
Call testfile
Main = DTSTaskExecResult_Success
End Function
sub testfile()
1 set fsys=CreateObject( "Scripting.FileSystemObject")
2 'msgbox(fsys.FileExists("C:\Documents and Settings\computer user\My Documents\DevShed\DTS\DTSTEXT\TextDts.txt"))
3 set file=fsys.GetFile("C:\Documents and Settings\computer user\My Documents\DevShed\DTS\DTSTEXT\TextDts.txt ")
4 msgbox(file.name & vbcrlf & file.DateCreated
& vbcrlf & file.Size)
end sub
The line numbers have been added for the sake of explanation, and are not created by the ActiveX Script Control. The function Main calls the procedure testfile. In line one, a FileSystemObject is created. An empty file TextDts.txt is then verified to exist in the second, commented out line, which returns true. In line three, the variable file is set pointing to the TextDts.txt file. The fourth line returns the name of the file, the date it was created and its size through a message box as shown below.
Modify the script task to copy the above file to a text file.
The Data Transformation task goes from source to destination. The TextFile (Destination) we created is of the destination type. Since we need to further process this text file to yield another text file, this file needs to be copied to a new file so that the copied file can become a source file. This is accomplished by adding the following lines to the testfile procedure above:
5 fsys.CopyFile file, "C:\Documents and Settings\computer user\My Documents\DevShed\DTS\DTSTEXT\Destin.txt"
6 set file2=fsys.Getfile("C:\Documents and Settings\computer user\My Documents\DevShed\DTS\DTSTEXT\Destin.txt")
7 file2.Attributes=32
The fifth line copies this file to another file called Destin.txt. The sixth line sets up file 2 to point to this Destin.txt file. Line seven sets up the file attribute of Destin.txt to archive (otherwise Read-Only). If the package were to be run at this stage (the SQL Server connection, the TextFile [destination] and the ActiveX Script task), the file TextDts.txt would be created, followed by the copying of the file to a newly created Destin.txt file at the desired location. Destin.txt is an exact copy of TextDts.txt, but it is not a destination type file in the context of DTS.
Next: Create a source text file >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
