Sometimes you need to automate the upload of a file to an FTP or SFTP. Thought I would share the way that I use the SFTP task in SSIS to upload files.

Firstly create the variables to use in the SFTP task. Variables are an easier way to manage things without going into the task, so you can change the password in the variable and not have to open up the SFTP task.

Next you need to complete the SFTP Task Editor.

What you need to take note of above that is very important is that you must remember to place the Remote File name. So this would be the name of the file you are uploading. Once the above is filled in you can click ok and your SFTP task should be ready to go without any red cross on it.

Example:
My ETL consists of generating a file to upload, then uploading the file and then sending a mail to notify users the files have been uploaded.

Hope this helps you with using the SFTP task in SSIS.

 

5 thoughts on “Execute SFTP Task in SSIS

  1. I read this post in the SQLServerCentral daily newsletter (20180725). I am wondering what version of SQL Server SSIS you are using and also, what SFTP Task you are using. If I am not mistaken, SSIS does not have an SFTP Task (even through SQL 2017) – just an FTP Task, so I’m assuming you are using a 3rd party or custom SFTP Task.

    Thanks!

  2. The free application WinSCP (https://winscp.net/) has a scripting component. You can compose scripts in its language and invoke it with CMDEXE (you can pass filenames on the command line), or you can use its library from C#, PowerShell, or whatever.

  3. How can we configure the task to receive multiple files such as a1.txt & a2.txt etc. I can configure for a single file, cannot see how to configure for multiple files, as this is looking for an object based variable

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.