Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SFTP in SQL Server Integration Services SSIS Package with Bitvise


By:   |   Read Comments (2)   |   Related Tips: More > Integration Services Security

Problem

SQL Server Integration Services (SSIS) does not support the Secure File Transfer Protocol (SFTP) in the FTP Task, however my organization's security policies require SFTP. Can I use a third-party tool such as Bitvise with SSIS to meet this requirement?

Solution

Yes, you can use a third-party tool such as Bitvise with SSIS. While there are many options with Bitvise and SSIS, I will demonstrate an example that I was able to setup and successfully execute in about 10 minutes. There are two sections to this tip, the first section describes the initial setup of Bitvise and the second section describes how to call Bitvise from SSIS.

First, I downloaded and installed the Bitvise SSH Client. In the Bitvise SSH client, I entered my host, user name, password and then checked the box to "Store encrypted password in profile".

Bitvise SSH client setup

Next, I clicked on the SFTP tab and then clicked on Login at the bottom to allow for the Host Key Verification. I selected "Accept and Save" and then the Bitvise SFTP Client user interface was displayed.

Bitvise Host Key Verification window


Bitvise SFTP Client

I exited out of the SFTP Client without transferring a file. Knowing the SFTP client was configured correctly, I clicked on "Save profile as" to save the profile to a file. The path and name of this profile file will be needed later.

With the SFTP client configured, the next step was to set up SSIS to call the Bitvise SFTP command line client. I created a new package in SSIS and dragged an Execute Process Task onto the palette as shown below.

Drag an Execute Process Task onto the palette

I double-clicked on the Execute Process Task to edit the properties of the task. On the General tab, of the Execute Process Task Editor I renamed the task to be more descriptive.

Rename the task in Execute Process Task Editor

Click on "Process" on the left side of the Execute Process Task Editor to display the Process settings. Bitvise SSH Client has a command line SFTP executable that we will execute.

  • Set the Executable property to the full path and name of the sftpc.exe file: C:\Program Files (x86)\Bitvise SSH Client\sftpc.exe
  • Set the Arguments property for two arguments. The first argument will accept the profile file created above using the -profile argument followed by the full path and file name. The second argument is -cmd which is then followed by the commands one would use from the command line separated by semicolons. In this tip, we will change the directory and then get a file.
  • Set the working directory to the directory where you would like the file to reside after the get command.
  • Set the WindowStyle to Hidden.
  • Click on OK to return to the SSIS Control Flow palette.


Set the processing properties in Execute Process Task Editor

Next, I tested the SSIS package in the Visual Studio debugger.

Run the SSIS package in debug mode

After the package executes, check the file system to make sure the file was transferred successfully. If you have trouble, you can troubleshoot with the Bitvise SSH client.

File transferred successfully
Next Steps

Take a few minutes to explore the different command line options available in Bitvise and how they work with SSIS.

Also, check out more tips on SFTP in SSIS on MSSQLTips.com.



Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, September 17, 2015 - 3:46:55 PM - Tracy Oliver Back To Top

Dallas,

 

I have had great success using WinSCP. It functions pretty much the same as BitVise and can be used in SSIS as a process task. Nice article. It is always good to know if alternatives exist.

 

Regards,

 

Tracy


Thursday, September 17, 2015 - 3:20:48 PM - Scott Back To Top

You might mention that BitVise is only free for up to 4 users/machines, after that you are supposed to buy licenses.  WinSCP is a free open-source SFTP client that could be used the same way (set up connections in the client GUI, then call it from an Execute Process task).

I prefer the Pragmatic Works Task Factory SFTP client.  It does require a license per server, but it has a lot of useful tasks and the SFTP task is completely configured within the SSIS package.  ("server" means a computer that will run packages via SQL Agent or dtexec, which requires a license.  There is no limit on the number of users or machines that can run packages in BIDS with no license.)


Learn more about SQL Server tools