Using The Data Profiler Task and FTP Task in SQL Server 2008 Integration Services

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.

]]>

Leave a comment

Your email address will not be published.