Solution for implementing SFTP with SSIS Packages

I had this situation in my project where I should update my SSIS package to retrieve files from SFTP site rather than the regular FTP. The problem starts here, SSIS does not support SFTP protocol by default. I did made a lot of search on internet and all my friends. What I could figure out was that the only method is to use third party extensions (which are definitely pricey). My requirment was very simple, only need to download files for which it was not feasible to purchase a third party software. After lots of research I would do it with a work-around. I though my experience would be useful to someone else.

Solution
My solution goes like this. There's a free tool called WinSCP which provides command line utility to communicate with SFTP sites. Just need to understand the scripting language for command line and execute the executable from SSIS.

What I did is something like this:

1. Installed WinSCP client utility from http://winscp.net/eng/download.php
2. Command line utility is located at C:\Program Files\WinSCP\WinSCP.Com
3. Create a script file (say myscript.txt). The following sample might give you some idea.

option batch on
option confirm off
open sftp://user:password@ipaddress:port
cd mydir
option transfer binary
get *.dat c:\mylocaldir
# Move the contents on remove folder
mv *.dat ./backup/*
close
exit

4. Invoke the WinSCP.com executable from SSIS (or a scheduled task in windows) with the following syntax.

"C:\Program Files\WinSCP\WinSCP.com" -script=c:\myscript.txt

---------------------------------------------------------------
This methodology worked for me. I hope this information will be usefull to someone. If you have any queries, please email me. I will be glad to help you.

17 comments:

Anonymous said...

The CozyRoc solution is not pricey, considering the components it brings in addition to the SFTP component implemented 100% in .NET. In your solution, you have to allow DTExec to include "executable permissions" to be able to execute third-party executables like WinSCP, making your server exposed to all kind of nasty problems down the road. You haven't probably spent too much time in the forums with people discussing problems running WinSCP as part of your solution. Is the additional effort less than the price you pay for peace of mind and support? I don't think so.

Clement said...

Yes, your are right. Using a 3rd party library is always the best solution. This is one of the approaches to a totally "cost-free" solution. Its upto the developers to take trade-offs depending on their requirements.

Its not just CozyRoc, there are a lot of cool 3rd party products that we may choose from. Each of them come with their own advantages and disadvantages.

Anonymous said...

It will be totally "cost-free" solution, if you don't have to pay for the Windows Server OS and SQL Server 2005. It is a choice between I value my time and I want tested-solution or I have time on my hands to play and it may work.

Anonymous said...

Gee, anonymous, do you by chance work for CozyRoc?

Anonymous said...

Hi,
It would be of great help if you could pass on the code of how u have written the SFTP and which task u have used to geth this done
Thanks in advance.

Anonymous said...

Dude, he totally did. It's all in the script file.

This is cool, will do the automation, but the part the CozyRoc employee left out is that you are not capturing error returns - which, you know, you could do. An SSIS script task should always return success or failure and there's no way to know which it was unless you assign the "shell" process' return to a variable and grep through it for definite success or failure tags (is my UNIX side showing or do I have to throw in an AWK reference too?) - you are closer to getting everything you need from this pig than you think, bone up on your RegEx and finish it!

Megha Barpande said...

Hi there,

Good to read your article on SFTP from SISS.

I have installed WinSCP on my local machine.

I dont use WinSCP.ini. In the preferences section of UI, it indicates that Configuration settings are saved in Registry.

I created a test.dtsx package with an Execute Process task. I configured the process as "C:\Program Files\WinSCP\WinSCP.com"
Arguments: "-script=C:\myScript.txt"

Within this script file I open a connection to the SFTP server:

open wave1@172.00.000.00 - privatekey=c:\wave\SSH Key\Privatekey.ppk

Then i upload some test files to the SFTP server.

When I run the SSIS package, it goes all fine.

Now in my actual application I create txt files which have data from my database.

my project folder where all the txt files are extracted is=D:\Projects\Wave\OUT
SSIS folder is=D:\Projects\Wave\Wave.SSIS\Extractdata.dtsx

Now I have got a similar set up for the Execute Process task.

Except that now my Script file resides in=D:\Projects\wave\Wave.SSIS\Script.txt

And instead of Uploading files from C:\ it has to upload from D:\Projects\Wave\OUT\*.txt

I have a config file for the package.

In my config file I specify the SQL Connection, OutPut folder, Script file location so that the package is portable and easy for deploying on test /production server.

The script.txt file itself has following:
open wave1@172.00.000.00 - privatekey=D:\Projects\wave\SSh Key\privatekey.ppk

When i run the package, I get error in SSIS. The output window says that the exit code "1" was expected however theer was exit code "0". Also, the files are not uploaded.

I have no idea what I'm missing. If I have everything on C:\ it works fine! but doesnt work on d:\

Is there any permissions issue?

I will appreciate if you can guide me.

Thanks for ur time and assistance in advance.

My appologies for sucha long question, just wanted to be clear regarding what I'm trying to achieve.

you may reply to me on : megha.barpande@telecom.co.nz
meghaj9@hotmail.com

Thanks,
Megha

Clement said...

I see no reason why it should not work.

Are you able to execute the script manually from command prompt? If it works fine, then there can be either of the following issues.

a) The package is not able to find the correct path from configuration

-Try hard-coding the settings in package and not using the configuration file.

b) There might not be permissions on "D" drive.
-Are you facing the problem only when you run the package from SQL Server or from VS IDE itself. For SQL Server you ensure that the System Account that runs the package is having enough permission on the folders.
-Try running the package on another machine
-if the problem occurs within the IDE itslef, then try to debug and see the values that are being passed to the "Execute Process task"

Finally try this to figure out what is the output of the tasks.
-Temporarily change the executable in Execute process tasks, as follows.

Executable: C:\WINDOWS\system32\cmd.exe

Arguments: /K "[CompleteWinSCPCommandWithArguments]"

The /K option ensures that the DOS window stays on after the task is complete. You should be able to see what exactly happened.

---------------------------------
That is all I can think of, do let me know if you find the solution.

Best of luck

Megha Barpande said...

Hi Clement,

Thanks for the reply.

I'm testing the package from VS IDE. The job has not yet been scheduled on SQL Server.

When Config file didnt work, I hard-coded the arguments for the Execute process task, but it made no difference to the output (it still errored).

Even when i was using the config file, i could see that correct arguments were getting passed, so dont know why it didnt work.

I'm sure theer will be a very silly solution to this problem.

Its saturday in NZ now. I may not be able to complete a live a test, however I'll definitely do it on Monday and keep you updated.

Thanks for your time and patience again.

Megha

s98ssr said...

I have a Stored Procedure that calls an SSIS package. It works great. Along the same lines, I have created another SSIS package to ftp a file using WinSCP. The package by itself runs great and transfers the file. However, when I run the package through the stored procedure, it fails. I tried different ways but with no resolution yet. I had the SP display the command it is executing and ran that from the command line and it works. Just can't get it to work from the Stored Procedure. Any ideas or help is greatly appreciated.

Anonymous said...

It sounds like the account that you're using to run the stored procedure doesn't have the proper permissions.

I would check to see what account is being used when you run the package by calling it in the stored procedure, and then see what account is being used when you run the package yourself. Compare the level of permissions and make changes as neccessary

mattcushing@gmail.com said...

great article, I had a question - in SSIS, what kind of task should I use to call the file? Is it a file system task? Trying to keep everything all in one place, so if I can create an SSIS task to kick off and get the file I need, I want to use the file contents in an import to a db.

any help would be appreciated.

Martin said...

Seeing that many people are trying to use WinSCP to implement SSIS SFTP transfer task, I have prepared a guide to SFTP Task for SSIS.

Samurai said...

Hi the post is very useful for me,
but i need some clarifications.

my requirement is download files from psftp through sql server procedure. so i written two batch file one for login into psftp and one for downloading psftp files to local folder. the batch files work fine if i click manually. if the same batch file executed in sql server with xp_cmdshell command it showing running but the sql server getting hanged. can you tell me which wayy method i have to use to compelte the above task and give me code if possible.

Siva said...

Hey Prashant,

Intially we have decided to purchase a third party tool which will handle SFTP transfers but after gng through u r article we have tested with winscp but the problem is the script is working fine to retrieve a single file from the sftp but its not working for *.* or *.csv and here's the script that I have used...

open batch on
open confirm off
Open 'SFTP connection'
Option transfer binary

cd direcotry
get *.csv path
close
exit

error message::
No valid incoming packet found
Error listing '/directory'
Session 'SFTP connection' closed.
No Session.


Let me know where we are doing wrong??

Thanks,
-Siva.

Siva said...

Hey Prashant

One more thing I want to share with you thats is file names and the file names have space in between and if we speicifies file itself then it was downloading but not for the *.csv

-Siva.

Vk said...

Hi Prashant,
I am a new SSIS Developer. I am creating a package which connects to a SFTP Server, for that I am using WINSCP with Stored Session, and accomplishes the below 2 Tasks.

1. get the latest.dat file (i.e. Todays date) from Home/XXX/latest.dat (SFTP Server) to my local directory D:\XXX\latest.dat and
2. I have to rename the file from latest.dat to latest.log and move the file to a Home/XXX/log/ folder on the remote server (i.e SFTP Server).

can you please help me with this?
Thanks in Advance