Using Data Transformation Services, part 7: Transferring Files with DTS

File Transfer Protocol (FTP) is a custom task in SQL 2000 Server’s Data Transformation Services. The files from an internet site can be transferred to a directory, or files can be transferred from one directory to another directory. This, in conjunction with the other DTS tasks discussed in the previous tutorials together with the workflow help in transferring the files easily.

FTP Task is used in replication for transferring snapshot files. This will be discussed in a forthcoming tutorial. For this tutorial, I will discuss very simple examples of FTP. These can performed in the DTS Designer discussed in earlier tutorials. Specifically, we will look at two of the allowed modes of transfer.

  • From an internet FTP site to a directory on the hard drive
  • From one folder to another in the same machine, or a to a shared drive
The FTP site

Before you transfer files from a FTP site, you should gather some information about the site. In IIS 5.1 (as with the case of XP Professional), the IIS comes with Web sites, FTP sites, and a default SMTP Virtual Server site. For FTP purposes, we will configure the Default FTP site. A virtual directory XPHTEK_FTP has been added whose files will be stored in a directory C:FTPSend on the hard disk, as shown in the following image. Further the authentication has been set to anonymous and does not require password.

The files and directories that are to be sent over FTP are stored in the C:FTPSend directory. The (virtual directory) XPHTEK_FTP directory may be browsed to look at the contents as shown here.

{mospagebreak title=File Transfer From FTP Site to Local Directory}

Create a new package in the DTS designer and add an FTP Task as shown in the next picture.

It adds an icon to the designer pane. Right click this icon. From the pop-up menu, click on properties to bring up the window shown in this picture. For the Description, you may choose to type whatever is appropriate. There are two options for the Source drop-down, internet being the default. It can be changed to a directory. For the present we keep the default and discuss the FTPing from directory to directory in the next section.

For the FTP site, you can enter either the localhost or the IP address. In this case, A remote site can also be referenced for example, as (fictitious site). However, you should have login information to connect to the site. Depending on the size and number of files to be transferred, you may have to retry several times. You can set this up in the Number of retries box with the dial. For the Directory, you may browse to a location. This location can be a local directory, as shown in this picture, or a shared drive using either a drive name, or a UNC path, such as \nechostftproot.

Next click on the tab files. If the internet site information is wrong or if the authentication supplied is not correct, you get the following error. You must rectify the information and try again.

If the connection is successful, you should see the files and directories in the C:FTPSend directory as shown in this picture. If you do not have List permissions to the <DIR> in the Source pane of the File Transfer Protocol Task properties window, you may not be able to expand the <DIR> folder to transfer the files therein.

Now, just highlight the files you want to FTP and click the > button. You may FTP all files using the >> button. If the file that you are trying to FTP already exist in the Destination folder, and you have not checked the checkbox marked Overwrite, you may get the following error.

If the file information is correct, you will get the following message when you execute the package (review previous tutorials for executing a package). The file gets transferred to the indicated directory on the hard drive.

{mospagebreak title=File transfer from directory to directory}

Choose Directory from the dropdown menu as shown. In this case, you indicate the path to both the Source and the Destination as shown in the next picture. In this case, files are being transferred from the Source, C:Documents and Settingscomputer userMy Documents, to the Destination, C:/FTPReceive folder.

Clicking on Files, in the File Transfer Protocol Task Properties window, brings up the window shown in the next picture. Here some three files have been chosen for transfer as shown. Clicking on OK completes the package definition.

The package can be executed in several ways as discussed in the previous tutorials, or as shown in the picture.

{mospagebreak title=FTP from Command line}

FTP task in the DTS Designer is the same as the FTP from command line in the DOS screen. FTP can be invoked by typing in FTP at command prompt. If you are not sure of the commands, you may type C:/ftp ?, to display all the commands. The next screen shows a screen shot of an interactive ftp session used to transfer a file from an internet site to a directory on the hard disk.

This tutorial has shown the steps necessary to transfer files using the FTP task in the DTS designer. This can be combined with the other tasks as well as workflow to carry out file transfer between systems.

One thought on “Using Data Transformation Services, part 7: Transferring Files with DTS

  1. We done those steps and it tell that the process was successful. But when we looked at the destination directory, the files were not there. Can you help us about this?

    Please send me an email.

[gp-comments width="770" linklove="off" ]