Download SFTP files using ssis

Question

I have files sitting in an SFTP location and I am trying to bring them to a shared drive using SSIS.
The problem with SSIS is that it has a task for FTP but not for SFTP.

Here is what I did for now.
I created an SSIS Package and used an execute process task to get this working.
I copied the WinSCP executable on my DEV server and I am using this in my Executable as shown in the pic below.

In my Arguments, I am using a txt file that has the SFTP HostName, Password, Source Path, TargetPath, and FileName to get the files downloaded.
When I run this, my SFTPfiles are getting downloaded in the specified path(C:\Users\kumarp\Documents)
My File names will be dynamic every day. For today it is DD_Adjustment_HOLY_2022-11-21.csv and tomorrow it will be DD_Adjustment_HOLY_2022-11-22.csv.
Can you help me with how I can change the file names in the .txt dynamically daily if this is the right approach to download the files from SFTP?

If not I will also welcome any better ideas.

Answer 1

Hi @reddy421 ，

You may try to use script task to do that.

Define the file name with expression as below

"DD_Adjustment_HOLY_"+SUBSTRING((DT_WSTR, 30)getdate(),1,4)+"-"+SUBSTRING((DT_WSTR, 30)getdate(), 6,2)+"-"+SUBSTRING((DT_WSTR, 30)getdate(), 9,2)  

Reference here: https://stackoverflow.com/questions/66242015/download-sftp-file-using-ssis-package

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.