Using SFTP with SQL Server Integration Services

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


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?


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.

Connect to SFTP site using Filezilla

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. Navigate to the DataDump folder in SFTP

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. Download PSFTP from PUTTY downloads page

Step 2: Create a Batch file

Create a new file in the local folder and enter the following code: Create a Batch File
  • 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. Save as Batch File

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. Create SSIS Package with Execute Process Task

Right Click and select Edit, open the Process tab from the Execute Process Task Editor. Execute 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. Execute Task Editor validation

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. Execute Task Editor running PSFTP

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

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. Filezilla PSFTP download confirmation.

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. SQL Agent gets hanged

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. SQL Agent gets hanged due to Prompt

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. SQL Add to Intranet Sites
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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Junaith Haja 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

Comments For This Article

Monday, August 1, 2022 - 8:17:01 AM - Sujithra Back To Top (90327)
Hi, I have a file on My Server that I need to Upload the file to the other SFTP server. So changing just the Put command will work. Also, all the options which are mentioned (-b,-be) are not recognized. Please help.

Wednesday, July 13, 2022 - 9:17:11 PM - Jake Back To Top (90256)
Solution to the batch run error:

Wednesday, January 19, 2022 - 2:51:13 AM - Amit Back To Top (89676)
Hello Author,

This is really a good article no doubt. However, one question, if connection fails during connecting to SFTP site, SSIS shows you the full command with password. In this case password is visible. If we are reporting the errors, so these details should not in the error logs.

Could you please advise what can be the solution in this case?

Thursday, July 25, 2019 - 6:27:54 AM - sandeep Back To Top (81862)


I am able to download file from remote to local destination but file is blank. Why data is not loaded in local file.

Wednesday, June 19, 2019 - 5:27:42 AM - Srivatsan Back To Top (81517)

Hi, I would like to know what is the information that you have entered in the bat file.

Can you please post that information too.

Tuesday, March 12, 2019 - 8:59:12 AM - Raju Back To Top (79267)

How to pass the parameters as a filename to the Batch file?

Can we achieve this without the batch file? If so what are the commands?

Friday, January 4, 2019 - 9:08:43 PM - SheFixesThings Back To Top (78634)

 Also, MUCH THANKS to Junaith Haja; this is an excellent tutorial! thank you again

Friday, January 4, 2019 - 9:07:42 PM - SheFixesThings Back To Top (78633)

 Sadly @ovid your fix for the generic error did not work for me. I'm trying to revise this to be a PUT instead of a push. I have a connection and the cmdline modifications you suggested connect and transmit fine; but the SSIS 2012 keeps returning the generic error. 

Thursday, December 6, 2018 - 12:15:07 PM - Marcos Back To Top (78415)

 Great! The last comment is the true!!! Now it's working! Thanks

Thursday, December 6, 2018 - 12:24:45 AM - Ovid Back To Top (78407)

For those confused by the generic 'The process exit code was "1" while the expected was "0"' error:

Outside of SSIS, run the psftp.exe process on the command line with the arguments required but WITHOUT the '-batch' command (for example):

psftp.exe [email protected] -P 12 -pw abcde -be -b mysftpfile.bat

It will prompt (ONCE) to ask if you trust the host key for the server you are trying to connect to, confirm 'y'.

Close the cmd prompt. The process will now work from the SSIS package.


Friday, October 26, 2018 - 2:01:35 PM - kamallendra vattikuti Back To Top (78071)

Hi There downloading from SFTP works fine. But what if I want to place file in SFTP.

Wednesday, September 12, 2018 - 3:38:38 PM - Koen Verbeeck Back To Top (77527)

David, I tried the solution provided in this tip in SSDT 15.7 (for VS 2017). I could get it to work in SSDT itself, but haven't tried scheduling with SQL Server Agent yet.

The exit code being 1 instead of 0 can mean any number of things. It means something is wrong and SSIS itself doesn't know what. It might be permissions, it might be connectivity, but without decent logging or error message there's no way of knowing.

Regarding other questions, you can find more info about Putty PSFTP on this page:

The swith -P can be used to specify a port number.

Tuesday, September 4, 2018 - 10:54:54 AM - Padma Raju Back To Top (77359)

How do you pass a filename as a variable?

Tuesday, May 15, 2018 - 5:03:24 PM - David Back To Top (75950)

 This doesnt work. Can you answer some of the questions below or delete this post its good information but doesnt give a resolution for the questions below

 Does this work? Seems al ot of people have the same issues. You really should have posts that dont work or need better explanations. At least have some answers or REMOVE the tip

The process exit code was "1" while the expected was "0".

Pretty much a waste of time

Tuesday, February 6, 2018 - 6:22:07 PM - pady Back To Top (75119)


 If i want to pass the port number what is the command

If i want to copy the entire directory what is the command.


Please help me.


Tuesday, May 16, 2017 - 5:10:15 AM - HARSHIT RATHORE Back To Top (55802)

  Code: 0xC0029151 :- The process exit code was "1" while the expected was "0". Sql Server Job giving this error. when we try to connect on ftp through Wincp via ssis. but job fails because of this error.

Thursday, March 30, 2017 - 1:15:19 PM - Mike Back To Top (53968)

 This doesn't work with SSIS 2008.  None of the options are recognized, and a command for port should be included.  


Tuesday, November 15, 2016 - 8:48:19 AM - Thorbjørn Back To Top (43765)

 @Prithivirajan I too had this problem, and for me it turned out to be related to HostKey of remote server.

PuTTY chaches remote hostkey in registry of current user.

When running an agent job as a proxy, SSH seems to be unable to access this registry entry of the (proxy) user.

One possible solution is to pass the hostkey as a parameter in the argument list to the psftp process; -hostkey


Monday, June 6, 2016 - 2:52:42 PM - prithivirajan Back To Top (41620)


 How to pass port number in it .


Please send the command 

Monday, June 6, 2016 - 10:39:04 AM - Prithivirajan Back To Top (41618)



The process exit code was "1" while the expected was "0".  

Please let me know if you have any suggestions

Friday, April 1, 2016 - 10:01:19 AM - Bhanu Sisodiya Back To Top (41118)


    kindly please give me solution.

    Can we use Multiple SFTP Location From a single 'Execute Process Task' if Yes then how.

    I mean 'mssqltipsuser@localhost -pw password -be -batch -b MSSqlTipsTest.bat' can we using one or more Credintion here or not.

Hope better response


Monday, February 1, 2016 - 3:52:57 AM - Vijay Back To Top (40561)

Hi Junaith,


Thanks for the wonderful info.But I get the following error when I use the same. 


The process exit code was "1" while the expected was "0".  

Please let me know if you have any suggestions






Monday, December 28, 2015 - 4:20:58 PM - Belinda Back To Top (40319)

Thanks for your post.  I have a few questions if you could help...

I have set an executable to GetFile.bat, which runs my bat file and downloads files from an ftp site as expected from within an Execute Process Task.  Where I"m getting stuck is if there isn't a file to download from the FTP site, the Execute Process Task fails with error - The process exit code was "1" when "0" was expected.  In this scenario, I would like to instead send an email notification rather than havng the task/package fail (but only in the case of no files to download, not other ftp errors)  I'm a bit of a SSIS newbie and need some specific details around making this solution work. I believe the answer may lie in a script task, but again, have little experience with writing script tasks and incorporating them into the package.  I should mention I'm using SSIS 2012.  Please let me know if you have any suggestions or if I can provide further information.


Monday, December 7, 2015 - 3:32:28 PM - Sandeep Back To Top (40207)

Thanks a lot for your comments.

here I have question. how we can make this process automated? we trying copy a scrubbed data and copy to QA. This we want to do in month bases. so how we can fix with automated process..


Friday, May 15, 2015 - 3:38:34 PM - Junaith Back To Top (37183)

@kenneth: Se my last suggestion to add your network drive where you have the psftp.exe file to the trusted intranet sites in your IE browser.

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

Thursday, May 14, 2015 - 2:43:40 AM - Kenneth Shi Back To Top (37171)

My company is trying to do the same thing (i.e. calling PSFTP in SQL Agent).  We tried your suggestion to have SSIS package calling it and set window to hidden.  But it didn't work.  The process still won't terminate. 

We also tried to write a C# EXE to  call the PSFTP and have windows hidden.  The windows will still pop up.  Same result.  And I guess this is why using SSIS package also didn't work.


Any idea ?



Wednesday, January 21, 2015 - 12:19:54 AM - Junaith Haja Back To Top (36003)

@Dave Clark: Thanks for your comment :)

Sunday, January 11, 2015 - 7:54:29 PM - Dave Clark Back To Top (35896)

Very good article Junaith.  I do the same with WinSCP.

I was not aware of the WindowStyle option.  I'll keep that in mind anytime I use an "Execute Process" task.


Dave Clark

get free sql tips
agree to terms