Using DTS and FTP to Push Files

By Guest Authors on 13 January 2003 | Tags: DTS

This article comes to us from Bill Twomey. Bill writes "I had to automate a daily extract process. This job had to extract a set of records, and deliver them to a remote system. At first this seemed like an easy task, but the requirements of outgoing transfer, a new filename each day and verifying delivery, made the assignment interesting. What’s so interesting since DTS gives you an FTP task? The FTP task does not deliver files. How do you verify the delivery? The FTP task will not do this. I resorted to using FTP in another way. Along the way, the assignment became a challenge to get the package to handle the daily-changing filename."

The package first sets up some controlling variables and files in the ActiveX Script task “Setup for Today”. The name of the output file is initialized to “Orders.”+today’s date. Assignments like this can be done in a Dynamic Properties Task using a SQL query. But because some Global Variables are constructed by string concatenations using other variables and constants, it was easier to assign them inside an ActiveX Script Task. You can set a package global variable in an ActiveX script this way (line 15 ):

DTSGlobalVariables("TransferFilename") = DTSGlobalVariables("TransferFileDir")
  & "\" & DTSGlobalVariables("TransferFileNameRoot") & 
  DTSGlobalVariables("Datestamp")

The FTP process has to be told what file to send. This can be done from the command line using a control file. Since the filename changes daily, you have to write a new control file. This is done using the FileSystemObject. The code to write a the control file is:

set oFSO = CreateObject("Scripting.FileSystemObject")
set oFile = oFSO.OpenTextFile( DTSGlobalVariables ("TransferFTPCommands").Value , 2, 1)
oFile.writeline ("open " & DTSGlobalVariables("TransferFTPServer"))
oFile.writeline DTSGlobalVariables("TransferFTPLogin")
oFile.writeline DTSGlobalVariables("TransferFTPPassword")
oFile.writeline "cd incoming"
oFile.writeline "mput " & DTSGlobalVariables("TransferFilename").Value
oFile.writeline "quit" 
oFile.Close

The FTP client does not provide convenient diagnostics. We needed to know if transfer was successful. Using the stock ftp.exe, the most reliable method is to visit the remote system and get a list of files there. I obtain the directory listing through another FTP session. This FTP session is managed by another control file created in the ActiveX Script task. The code to create this control file is:

' Generate an FTP command file to verify that transfer worked.
set oFile = oFSO.OpenTextFile( DTSGlobalVariables ("TransferFTPVerifyReq").Value , 2, 1)
oFile.writeline ("open " & DTSGlobalVariables("TransferFTPServer"))
oFile.writeline DTSGlobalVariables("TransferFTPLogin")
oFile.writeline DTSGlobalVariables("TransferFTPPassword")
oFile.writeline "cd incoming" & vbCRLF & "ls -l " & vbCRLF & "quit" 
oFile.Close

The last block of code in the ActiveX Script sets values used in the steps that verify transfer. We’ll know today’s file transferred successfully if it is listed in tempdb.dbo.RequestFiles. The query to search for today’s file name in RequestFiles is built with:

tmp = "Select count(*) from tempdb.dbo.RequestFiles " & vbCRLF & _ 
  "Where tempdb.dbo.RequestFiles.Filename like '%"+ filename + "%'"

Later in the package, a Dynamic Properties Task evaluates this query to set a global variable. Since the query is not known until run time, the Dynamic Properties task must be modified to use this query. To do this, I search through all properties of all tasks, until I find the assignment in a Dynamic Properties task that sets TransferredFileCount. Then this property is modified. The code to do this is:

for each task in oTasks
  if task.Properties("Description") = "Evaluate File Count" then
    ' set the Query in DynamicProperties Task so that it checks for today's file
    For Each oAssignment In task.CustomTask.Assignments 
      if instr( oAssignment.DestinationPropertyID , "'TransferredFileCount'" ) then
        oAssignment.SourceQuerySQL = tmp
      end if
    next
  end if
next

Notice that I need to modify a Dynamic Properties task, but the assignment goes through task.CustomTask.Assignments. This seems to be contrary to the object diagrams in SQL Books-On-Line. But the fine print says, “The CustomTask property of the Task object returns a reference to the appropriate custom task object.”

The “Verify File Transfer” Execute SQL Task runs the stored procedure VerifyRequest and obtains a list of files transferred to the remote system. In this stored proc, a directory listing is generated by running FTP using xp_cmdshell and the control file generated earlier file. The xp_cmdshell results are saved and cleaned up with this code:

set @cmd = 'ftp -i -s:' + @ftpcommandfile
Insert into tempdb.dbo.RequestFiles (Filename)
  Exec master.dbo.xp_cmdshell @cmd

Delete 
From tempdb.dbo.RequestFiles
Where coalesce(Filename, '') not like '%Orders.%'

A stored procedure is used because you can run a process, clean the results and check for success in one block of code. This is more convenient that creating a series of FTP and Execute SQL tasks.

Once the verification data is in tempdb.dbo.RequestFiles, the package obtains a count of files matching today’s file name. This is done when DTS evaluates the query saved in the “Evaluate File Count” dynamic properties task.

Once the package has a count of matching files on the remote system, it can take act to alert operators. For the purposes of this demonstration, you are notified by a msgbox. The package reports success or failure by exiting with a corresponding DTSTaskExecResult_Success or DTSTaskExecResult_Failure return code.

In a production system, I would combine the last three steps into one call to a stored procedure. This stored procedure would check the count of matching files and send alerts. These extra steps exist so that alerting can be done from the package.

This package runs in the Northwind database. To create it, you will have to download the VerifyRequests procedure and create it in Northwind. Download the FTPPush dts file and open it from Enterprise Manager. You’ll need to change the FTP global variables to work on your FTP server. After running, you can verify that the package did what you expected. Look at the package global variables. Initially TransferredFileCount=-1. After successful execution TransferredFileCount=1. Also open “Evaluate File Count”, you’ll see that has the text of the “select count(*)…” query was generated and stored in the ActiveX Script task.

What is special about this package? First, it gives you a method to transfer files to another system. Second, it demonstrates some useful ActiveX Scripts. Third it, it meaningfully demonstrates the Dynamic Properties task. Finally, it performs manipulations that the Dynamic Properties Task just cannot handle. This is a Dynamic DTS package!

Bill Twomey is a SQL DBA (MCDBA), working for Database Solutions of Cherry Hill, NJ. He lives in New Jersey and works in the Delaware Valley. You can reach him at Bill_Twomey@hotmail.com