Overview of the FTP Task
The FTP task in SQL Server 2005
and later version allows you to send and receive files and it also manage
directories on servers using the File Transfer Protocol (FTP). The different
types of operations which can be performed by FTP Tasks are shown in the
snippet below.
Send
Files: – Send files from your local server to the FTP server
Receive
Files: – Receive files from the FTP server
and save it locally on your server
Create Local Directory: – Create a folder
on the local server
Create
Remote Directory: – Create a folder on the FTP server
Remove Local Directory: – Delete a folder
which is available on the local server
Remove Remote Directory: – Delete a folder
which is available on the FTP server
Delete Local Files: – Delete a file
which is available on the local server
Delete Remote Files: – Delete a file
which is available on the FTP server
Worked example using the FTP Task
We will run through an example using an FTP
Task to receive the readme.txt file from the developr folder
of Microsoft.com. The FTP address from where the file will be downloaded is ftp.microsoft.com
1. Create a new SQL
Server Integration Services Project and rename the default package FTPTaskExample.dtsx
2. Double click FTPTaskExample.dtsx
package to open it up in Design mode.
3. Add variables named
FTPDestinationPath and FTPSourcePath; you can open up the Variables window by
right clicking within the control flow design window and by choosing the
Variables option from the drop down list as shown in the snippet below.
4. Once the variables
window opens up, click on the Add Variable button and provide the details for
the variable as shown in the snippet below.
5. Create a new folder
named FTPDestination within the C Drive. The readme.txt file which will
be received using the FTP Task will be saved in this folder location.
6. Right click within
the Connection Managers window and select New Connections… from the
popup window. This will open up the Add SSIS Connection Manager window,
select FTP and click Add… to configure the FTP Connection Manager.
7. Within the FTP
Connection Manager Editor window, under Server Setting set the Server Name to ftp.microsoft.com and set the Server
Port value to 21 which is the default port for most FTP sites. Under
Credentials set the User Name value to anonymous and leave the password field blank.
Under Options make sure to you have selected Use Passive Mode which allows you
to initiate the data connection to the FTP servers. If the Use Passive Mode
checkbox is unchecked then the FTP connection is in Active Mode and the server
initiates the data connection. Click Test Connection to verify the FTP
connection. Click OK to save the change in FTP connection manager.
8. Drag and drop FTP
Task from the toolbox to the Control Flow window and rename it “Download a
File Using FTP Task”, then double click the FTP Task to open up the editor
window as shown in the snippet below.
Within the FTP Task
Editor, click on General tab in the left panel and then from the right side
panel under Connection choose FTPConnection value as “FTP Connection Manager”.
9. Select the File
Transfer tab on the left side panel of FTP Task Editor and provide the details
as shown in the snippet below.
The
FTP Task Editor should be configured with the details as show below.
Local Parameters |
|
IsLocalPathVariable |
True |
LocalVariable |
User::FTPDestinationPath (Choose the user variable from the Drop-down list) |
OverwriteFileAtDest |
True |
Operation |
|
Operation |
Receive Files |
IsTransferAscii |
True |
Remote Parameters |
|
IsRemotePathVariable |
True |
RemoteVariable |
User::FTPSourcePath (Choose the user variable from the Drop-down list) |
Click OK to save
the changes made to the FTP Task Editor.
10. Finally, to execute the package, right
click the FTPTaskExample.dtsx package from the solution explorer and select the
Execute Package option from the drop down list. Once the package has
successfully executed you will be able to see the below screen within the
control flow designer.
11. Once the package has successfully
executed you will be able to see the ReadMe.txt files within C:FTPDestination
folder.
]]>