Shalini's Knowledge Share

Although there is a FTP task within native SSIS, it lacks both SFTP and FTPS tasks. There are 3 methods to access:

1. Using third party components – ex: zappySys, Cozyroc etc.

2. Purely code based

3. Using command line tools

Using a third party component is pretty straightforward if you have an understanding of how a normal task works in SSIS. Most often, you dont need to do any coding. However, usage of third party tools in some organizations is not the way to go. Using a code based approach can also create issues when multiple environments and servers are involved.

Due the above issues, I had no other choice but to use command line tools.

WinSCP

In my case, i used WinSCP, which is a quite popular FTP client and free that enables us to transfer files using FTP, FTPS, SCP, SFTP, WebSav and S3 protocols. I have outlined below how to access an FTPS location from an SSIS package:

1. Download and install WinSCP both in the server you are hosting your SSIS packages and also your local PC where you are developing.

2.Next start WinSCP in your computer and logon to the FTPS server. For this you should have a username and password. 

Once connected, you can manually download/upload documents to and from the FTPS location.

3. Next is the part where you connect to the FTPS location via SSIS. There are 2 methods.

    a. Via a script task

    b. Write a batch script and calling the batch script inside Execute Process Task.

Since you can easily get the code online or even via the WinSCP software for file transfers using a batch  script, I went for option 2. You can get this from Session -> Generate Session URL.

In my case, I\’m downloading any files in the FTPS location and once downloaded, I\’m moving the files in the FTPS location, into a different folder. You can get a full list of commands from https://winscp.net/eng/docs/scripting.

But here\’s what I have done.