By: Dallas Snider | Comments (2) | Related: > 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".
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.
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.
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.
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.
Next, I tested the SSIS package in the Visual Studio debugger.
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.
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.
- Using SFTP with SQL Server Integration Services
- SQL Server Integration Services SSIS FTP Task for Data Exchange
- SQL Server Integration Services (SSIS) Tutorial
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
