Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using SFTP with SQL Server Integration Services


By:   |   Read Comments (13)   |   Related Tips: More > Integration Services Control Flow Transformations

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


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.

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.

MSSQLTips.com 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.

MSSQLTips.com 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:

MSSQLTips.com 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.

MSSQLTips.com 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.

MSSQLTips.com Create SSIS Package with Execute Process Task

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

MSSQLTips.com 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.

MSSQLTips.com 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.

MSSQLTips.com Execute Task Editor running PSFTP

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

MSSQLTips.com 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.

MSSQLTips.com 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.

MSSQLTips.com 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.

MSSQLTips.com 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.

MSSQLTips.com 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 MSSQLTips.com


Last Update:


signup button

next tip button



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.

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 


SQL tips:

*Enter Code refresh code     



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

  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

 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

 @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 06, 2016 - 2:52:42 PM - prithivirajan Back To Top

 

 How to pass port number in it .

 

Please send the command 


Monday, June 06, 2016 - 10:39:04 AM - Prithivirajan Back To Top

 

 

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

Please let me know if you have any suggestions


Friday, April 01, 2016 - 10:01:19 AM - Bhanu Sisodiya Back To Top

Hi,

    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

Thanks


Monday, February 01, 2016 - 3:52:57 AM - Vijay Back To Top

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

 

Regards,

Vijay

 

 


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

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 07, 2015 - 3:32:28 PM - Sandeep Back To Top

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

@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

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

@Dave Clark: Thanks for your comment :)


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

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.

Thanks,

Dave Clark


Learn more about SQL Server tools