Using SFTP with SQL Server Integration Services

By: Junaith Haja   |   Comments (28)   |   Related: More > Integration Services Control Flow Transformations

Problem

My client has hired a vendor for a telemarketing project and they need to send us the collected data.  Since the data is customer information we cannot pass this sensitive information via email and the company's privacy policy won't permit the vendor to write directly to the SQL Server database. The solution we came up with is to use SFTP to get the files from the vendor and also use SQL Server Integration Services to load the data to the database. We want this to all be integrated into an SSIS package, but SSIS doesn't have a native SFTP option.  How can this be done?

Solution

SFTP stands for Secure File Transfer Protocol which is a world wide accepted secure protocol to transfer and access files over a secure channel. The data and channel are encrypted in SFTP mode, which prevents unauthorized access by any intruders and it's mainly used between companies to transfer secure and sensitive information.

You may be aware of the FTP task in SSIS which allows us to copy or paste files to/from a FTP site, but unfortunately SSIS doesn't support communication over SFTP. A work around for this will be to use PSFTP through an Execute Process Task in SSIS to download the file to our local machine. PSFTP is a SFTP client tool provided by PuTTy to transfer files between computer systems using the SFTP protocol.

A solution for this scenario will include the following steps:

1. Download PSFTP.exe file to our destination folder
2. Create a batch file with logic to download the text file using Windows command language
3. Create a SSIS package with an Execute Process Task to run PSFTP.exe

For our illustration, I have used the FileZilla client to connect to the SFTP site.  The below image shows us connected to our remote SFTP site.

The Vendor file is posted in the remote folder named DataDump and the file name is "TeleMarketingResults.txt"  The requirement is to download the "TeleMarketingResults.txt" to our local folder C:\MSSqlTipsDestination.

Step 1: Download PSFTP.exe file to a local folder

Go to the PuTTy Download Page and download the psftp.exe file to a local folder. In our example it is C:\MSSqlTipsDestination.

Step 2: Create a Batch file

Create a new file in the local folder and enter the following code:

• cd - is the Windows command to change the directory. We have to navigate to the folder named DataDump in the SFTP site, so we write the code as cd DataDump which will allow the process to change to the DataDump directory.
• get - is the Windows command to download the file from the server.  The general syntax is get FileName.format, so we have written the code as get TeleMarketingResults.txt, which will enable the batch file to download the file from the SFTP site.

Save the file as MSSqlTipsTest.bat in the C:\MSSqlTipsDestination folder.

Step 3: Create SSIS package with Execute Process Task to run the batch file

Open your BIDS/Visual Studio and create a new Integration Services project.

Drag and Drop an Execute Process Task from the SSIS Tool box Menu to the Designer.

Right Click and select Edit, open the Process tab from the Execute Process Task Editor.

• In the executable text box give the local folder destination where we put the PSFTP.exe file.
• In the Arguments, pass the username, hostname, password and the batch file we created.
  • -pw this is the password
  • -be this allows the batch to continue on errors
  • -b this specifies the file with FTP commands
• In the Working Directory, give the local folder destination where we want to download the file from the SFTP site.

Click OK. Now the Execute Process Task will have its arguments validated and the task will be shown without any validation errors as shown below.

Run the Execute Process Task and you will see the task launches a command window using PSFTP.exe and connects to the SFTP site using the credentials we provided as arguments and runs the batch file to download the file to our local folder. While running it will launch the PSFTP.exe like below.

Once it runs successfully, PSFTP will close by itself and it will complete execution like other SSIS tasks.

We can confirm the download via the FileZilla client by refreshing it. We can see below that we have the "TeleMarketingResults.txt" file downloaded to our local folder.

From here you can use a Data Flow Task to import the flat file into the SQL Server environment to complete our requirement.

You could also schedule the package for a daily download using SQL Server Agent.

When using SQL Server Agent the process may hang

You may run into a scenario where the package runs and gets hung up for a long time. SQL Server Agent will try to launch the DOS window which is the reason for the package to hang. In such cases, change the WindowStyle to Hidden in the Execute Process Task Editor in SSIS as shown below.

Also, not all SQL Server Agent accounts have permission to run packages that have an Execute Process Task, in such cases create a Proxy Account with Operating System(cmdExec) and SQL Server Integration Services Package subsystems and run the job through a Proxy Account or give the cmdExec permission to your SQL Agent account.  Here is another tip about how to create a Proxy Account.

Sometimes due to added intranet security in your organization, the Network Accounts are designed in a way to prompt for a security warning when they try to launch an .exe application via automation. This may also cause the job to hang when scheduled through SQL Agent.

To overcome this add the local drive to your trusted Intranet Sites by opening Internet Explorer and go to Internet Options > Security > Click Local Intranet > Sites and add your drive location as shown below.

Next Steps

• Try creating different batch files using these scenarios
  • Download multiple files from server - mget filename*.txt (we use the wildcard operator)
  • Upload file to a server - put filename.txt Upload multiple files to server - mput filename*.txt
  • Delete Remote Files - del filename.csv
  • Create remote directory - mkdir mynewdirectory
  • Remove/delete remote directory - rmdir olddirectory
  • Rename remote files - ren oldfile newname
• If you are able to accomplish this you have technically extended the functionality of SSIS
• Check out all of the SSIS tips on MSSQLTips.com

About the author

Junaith Haja is a Senior Business Intelligence Consultant with Browse Info Solutions, Inc and leads a Microsoft SQL Server and BI team.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips