Push FTP with SQL Server

Push Those Files Away!!!!

Introduction

When DTS was released, I thought this was a useful, but overhyped tool. After all, I'd spent seven years developing all types of SQL applications that made do with xp_cmdshell for anything that I couldn't do in T-SQL. Then last year, I started working for a company that used a "Managed" service for it's website. Bascially this means that we paid some company a rental fee for servers and they kept the servers running, backed up, patched, etc. in a data center. The data center I used happened to be in Dallas , which is fine. Except, I live in Denver . Kind of a hassle for hands-on work. Fortunately we were running on Windows 2000 and DTS can in very handy for building some data transfer applications.

The Problem

Recently we moved to another data center in Denver and upgraded to SQL Server 2000. Tada!!!!! Finally, DTS included an FTP task. I eagerly dug into DTS (with the aid of Professional SQL Server 2000 DTS) and got ready to develop an automated task to send the database backups to my development server each night.

I perform local backups from SQL Server and then need to copy these to a remote server. In the current setup, I wanted to copy them to a utility server on the local network, but a server that was not part of the domain (for security reasons). This makes transfers using standard Windows file copy utilities problematic. The solution that I wanted to use was ftp since this is a standard, open solution. At the present time, all my servers are Windows, but the possibility exists that we may implement some Linux or Unix servers.

One problem: The FTP task only "Gets" files.

How absolutely, completely, totally annoying! How much effort is there to "push" files when you have already written an object to "Get" them!!!!!!! (SQL Server Team, hint, hint).

The Solution

Never fear! I am not so easily deterred. When I was using a managed solution, the backups were performed once a day using a maintenance task. I created a batch file that would ftp the *.BAK files from the backup directory using the native ftp client in Windows 2000 and a scheduled task, I transferred this to my office and then had a separate batch file that would rename it. This worked great since there was only one backup file each day in the backup directory. In the new data center, however, we had cranked down the backups and were making one every six hours and keeping the previous 4. Plus I now needed to transfer copies of the backup files to a secondary server where they could be backed up to tape more often.

The solution was to leverage the ActiveX Scripting task as well as the Execute Process task in DTS. And a little understanding of the ftp client and batch file automation. Let's examine the ftp client first.

The ftp Client

If I wanted to automate the sending of a series of files, I can build a text file that contains the commands I want the ftp client to run. Of course, to do this, I need to know the file names of all the files. I then call the ftp client with the -s option and include the name of my text file. Suppose I wanted to send two files from my system (c:/test1.txt and c:/test2.txt) using ftp, I could create a text file that contains the following:

open ftp.test.com

MyUser

MyPwd

put c:/test1.txt

put c:/test2.txt

quit

I'd then save this as "c:/ftpauto.txt". If I then run the following command:

ftp -s:"c:/ftpauto.txt"

This command will connect to the ftp.test.com server. Once it connected, the next two lines send the user name and password. The remaining lines (before the quit) send specific files to the server. The "quit" command ends the session.

The test1.txt and test2.txt files would be sent to the ftp site automatically. This gets us the first part of the solution. We now know how to automate an ftp "put". Now on to the next part, determing which files to transfer.

ActiveX Scripting

I am sure most of you can guess this part, but it is a fairly simple exercise in the use of the FileSystemObject. If you have never used it, the FileSystemObject allows you to manipulate files and folders in the file system of a computer. This object is very powerful and includes methods for creating, deleteing, moving, copying, listing, etc. for both files and folders.

For my purposes, I needed to scan all the backup folders and get the latest backup files under each folder as well as any transaction backups that had not been transferred. The last transfer date was stored in a global variable in the package. This will be described below in The DTS Package. The other scripting was implemented in the VB Script below:

'**********************************************************************

' Copy latest backups

'************************************************************************

Function Main ()

        On Error Resume Next

 

        Dim strDestPath, fNewFile, strSourcePath, fOldFile, sBackup

        Dim fso, f, f1, fc, fcreated, fname, fldrItem, fldrName

        Dim objTxtFile, baseDate

 

        ' Initialize the variables

        strSourcePath = "c:/Program Files/MSSQL/Backup"

        strDestPath = "c:/SQL_Backups"

        fcreated = DTSGlobalVariables("LastXfr").value

        BaseDate = CDate( fcreated)

 

        Set fso = CreateObject("Scripting.FileSystemObject")

 

        ' Create the Text File

        Set objTxtFile = fso.CreateTextFile( strSourcePath & "/ftpSend.txt", TRUE)

 

        ' Write the Header

        objTxtFile.writeline( "open 192.168.1.104" )

        objTxtFile.writeline( "FTPSQLBackup" )

        objTxtFile.writeline( "SendItNow" )

        objTxtFile.writeline( "cd SQLBackup" )

 

        Set f = fso.GetFolder(strSourcePath)

 

        For Each fldrItem in f.SubFolders

 

               Set fc = fldrItem.Files

 

               fldrName = fldrItem.name

 

               fname = " "

 

               For Each f 1 in fc

                       If f1.DateCreated > BaseDate Then

                               objTxtFile.writeline( "put """ & strSourcePath & "/" & fldrname & "/" & f1.name & """")

                       End If

               Next

        Next

 

        objTxtFile.writeline( "quit" )

        objTxtFile.Close

 

 

        Set objTxtFile = Nothing

        Set fso = Nothing

 

        Main = DTSTaskExecResult_Success

 

End Function

This script really functions in three parts, which I will describe below.

The first part of the script writes the header portion of the ftp file. This part includes the lines

        ' Create the Text File

        Set objTxtFile = fso.CreateTextFile( strSourcePath & "/ftpSend.txt", TRUE)

 

        ' Write the Header

        objTxtFile.writeline( "open 192.168.1.104" )

        objTxtFile.writeline( "FTPSQLBackup" )

        objTxtFile.writeline( "SendItNow" )

        objTxtFile.writeline( "cd SQLBackup" )

These lines create the file and then write the ftp server address, the user name, the password, and then change the destination directory.

The next section is designed to loop through all the folders in my backup folder (stored in strSourcePath). First I create a handle to work with and then set the "f" variable to the folder handle. I then

        Set f = fso.GetFolder(strSourcePath)

 

        For Each fldrItem in f.SubFolders

 

               Set fc = fldrItem.Files

 

               fldrName = fldrItem.name

The DTS Package

The DTS package is built by including the ActiveX task above. In addition, there is a global variable in the package that stores the last transfer date for the set of backup files. I populate this using the dynamic properties task to read a datetime value from a table and update the global variable. This allows me to send files that were created after a particular datetime.

The Job

The last part of the solution was to implement a job under SQL Agent. This job consisted of two steps: one to run the DTS package and create the ftp file and the second to run the ftp client with the -s parameter and the file name created in part 1.

Conclusion

I know there are any number of COM wrappers over utilities that will implement this same functionality. However, these solutions have two downfalls for me:

1. Using a third party utility adds complexity, requires installation, and adds additional potential that something will fail on my server.

2. These solutions cost money. And if I can develop my own solution, I'd rather do that.

This solution gives me good control over the transmission of the ftp files. It also keeps the solution inside SQL Server. I am taking advantage of only components that exist on all my SQL Servers without having to install any additional software.

As always, I am sure some of you will have great ideas to extend this technique or will point out any flaws. Please use the "Your Opinion" button below and please rate this article.

Steve Jones
October 2001