using Rebex.Net;
// News, tips & tricks from Rebex developers

HOWTO: Registering SFTP and FTP/SSL for use in SSIS package

  |   Martin Vobr
FTP HOWTO SFTP SSIS

 If using SQL Server 2012 Data Tools for Visual Studio 2012, use an updated version of this blogpost. 

The SFTP and FTP/SSL component included in File Transfer Pack was not especially designed to run inside the SSIS (SQL Server Integration Services). However, with little effort both components can be used from inside the SSIS "script task".

Registering the component for use in BIDS 2005

Unfortunatelly, the "Add Reference" dialog in the BIDS (Business Intelligence Development Studio 2005) does not allow adding reference by browsing the assembly on the disk. It offers pre-registered assemblies only.

To register the component for use in BIDS:

  1. Copy Rebex assemblies (DLLs) into C:\Program Files\Microsoft SQL
    Server\90\SDK\Assemblies      - this should make them appear in the Visual Studio for Applications "References" dialog. 

    NOTE: On 64bit Windows use the " C: Program Files (x86)" folder even if your SQL server is 64bit. The DLLs will be retrieved from VSA and it is not 64bit application. 

  2. Add Rebex assemblies into Global Assembly Cache (GAC) - this is
    needed in order to make it possible for SSIS to run them.

  3. One user has also reported that copying the assemblies to
    C:\Program Files\Microsoft SQL Server\90\DTS\Binn instead of putting them into the GAC is sufficient too. Similar problem is discussed in newsgroup post on Microsoft website.

NOTE: for production environment (when not modifying but only executing the task), the only GAC registration is needed.

Registering the component for use in BIDS 2008 

The Business Intelligence Development Studio 2008 no longer uses VBA for editing script tasks. The common VS.NET with a common "Add Reference" dialog is used there. So the trick with the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder is not used for SSIS 2008.

However, you still have to add your libraries to GAC for proper executing of SSIS 2008 packages.

Using Rebex component in SSIS package

  1. Add new Script Task
  2. Modify the script via the "Design script" in "Properties dialog".
    The Visual Studio for Applications IDE pops up.
  3. Add reference to the DLLs in the Project Explorer window.
  4. Add the code to connect, transfer files or do whatever you want as
    described in SFTP and FTP/SSL tutorials.

Adding the references for the SSIS Script Component (used in data flow task) is similar.