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.

Pages: 1 2




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |