Using SFTP and FTP/SSL in SSIS package in VS 2012

SFTP and FTP/SSL components (included in Rebex File Transfer Pack) were not especially designed to run inside the SSIS (SQL Server Integration Services). However, with little effort component can be used from inside the SSIS "script task". The FTP/SSL component was chosen as a model for this sample – another component can be used analogously.

This article shows a sample for the SQL Server Data Tools 2012 (part of SQL Server 2012, installed as a plugin to Visual Studio 2012). Older versions - the Business Intelligence Developement Studio 2008 or 2005 are supported as well - see a separate article for specific configuration on the older platforms.

The sample is demonstrated on the following steps: 

1. Open VS 2012 and create a new "Integration Services Project".
   

   A new, empty SSIS package is created along with the new project.

2. Insert a "Script Task" from SSIS Toolbox to the Control Flow window.

   

3. Open Script task properties (by double-clicking - not by right
   button click) and choose "Edit Script".

4. Add the required assemblies (Rebex.Ftp.dll and its -
   Rebex.Common.dll and Rebex.Networking.dll dependenies in this case) into Global Assembly Cache (GAC).
   This should be done on any computer the SSIS package is intended to run! Hint: use gacutil –i assemly.dll (details here)

5. Add references to these assemblies in the newly opened script
   project.
   

6. Write the body of the script code:

   using (var client = new Rebex.Net.Ftp())    
   {
     string ftpHost = "ftp.someServer.com";
     string username = "user";
     string password = "password";
     string content = "Hello world!";
     string remotePath = "folder/file2.txt"; // be sure 'folder' exists
   
   
     // connect ang login to an FTP server
     client.Connect(ftpHost);          
     client.Login(username, password);
   
   
     // sample FTP transfer - upload a text to file on FTP server 
     byte[] data = System.Text.Encoding.Default.GetBytes(content);
     System.IO.MemoryStream ms = new System.IO.MemoryStream(data);
     client.PutFile(ms, remotePath);
   }
   

7. Return to the SSIS project and run the package (F5 shortcut).