SSIS : Download files from SFTP using SSIS Execute Process task and Putty (PSCP)

Here i want to explain how to download files from SFTP location  using SSIS. There are different ways but i want to tell you how we can do it using PSCP .

use a free tool named pscp. You can download the tool here: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

First all place pscp.exe in folder C:\Program Files\PuTTY\pscp.exe ( generally if you have Putty installed on your machine that pscp.exe is placed in that folder only )

First check the command using cmd promt

navigate to the folder where Pscp.exe exists
Then type the below command

pscp -sftp -pw <<password>> <<username>>@IP:Sourcepath DestinationPath

pscp -sftp -pw Password Username@11.111.11.1:/opt/DSC_6_0_R5/logs/metrics.csv C:\Users\Public\Documents\SurendraData\DRA\PSCPTest\server1A_metrics_SSIS.csv

NOTE : if you use the above command you will need to give respond yes or no in the next step .


Now Coming to SSIS

1.Open the SSDT or BIDS and create a new SSIS Project.

2.Drag and drop the Execute Process Task to the design pane. This task executes any process file. In this tip we will execute the pscp.exe file.

3.Double click on it and click the process Tab. Fill the Executable field, the WorkingDirectory and the Arguments

The Executable file is the name of the .exe file. In this case the pscp.exe.

The WorkingDirectory is the path where the pscp.exe is located.

Finally the Arguments are the parameters used.

Finally the Execute Process task looks like