SSIS SFTP task examples to upload, download, move and delete files / folders

Introduction – Using SSIS SFTP task (FTP, SFTP, FTPS)

In this article, we will show SSIS SFTP task examples. This task supports multiple protocols (Classic FTP, SFTP, FTPS).

Uploading files using SFTP or downloading files are common requirements in SSIS. In order to work with FTP, Microsoft SSIS includes the FTP Task that allows to upload files to FTP, download files, creating local and remote directories an more. However, there is no native task to work with SFTP. Also Native FTP task doesn’t support many advanced options such as FTP over SSL (i.e. FTPS).  In this article, we will show how to use the SSIS SFTP task included in ZappySys SSIS PowerPack.

ZappySys SFTP Task will let you send files to SFTP/FTP/FTPS server, receive files from SFTP/FTP/FTPS server. With ZappySys SFTP Task, you can not only create/delete files and folders on ftp server but also get file properties (creation date, last modified, last read dates, size and more), verify if the files exist, rename and move files and more.

SSIS SFTP Task Video Tutorial

The following video shows a nice tutorial about a ZappySys SSIS SFTP Task with examples:

Requirements

1. First, you will need to have SSIS installed
2. Also, make sure to have SSDT
3. Finally, do not forget to install ZappySys SSIS PowerPack

Whats is FTP, FTPS, SFTP, FTP/SSL?

Many people still have confusion over various protocols around FTP terminology. Most common terms you hear are as below.

• FTP
• Classic FTP
• Secure FTP
• SFTP
• FTPS
• FTP/SSL
• FTP over SSL
• FTP Over SSH

You must be overwhelmed by now 🙂 … So are they all same or different? See below for quick clarification in nutshell.

Mainly there are 2 protocols for FTP and yes they are very different but still used to do Files Transfer to / from remote server.

1. Classic FTP – sometimes referred as just FTP (Typically server runs on Port 21)
2. SFTP – Sometimes referred as Secure FTP or FTP over SSH (Typically server runs on Port 22)

So in short SFTP is not same as Classic FTP protocol. SFTP is more secure because traffic is always encrypted. On the other hand Classic FTP is not encrypted by default but you can use FTP over SSL to request encrypted traffic in classic FTP. FTP over SSL also referred as FTPS or FTP/SSL.

Getting Started

In order to start, we will show several examples. ZappySys includes an SSIS SFTP task that will allow to work with SFTP, FTP or even FTPS. Let´s start with an example. In this SSIS SFTP task example, we will show how to download files from SFTP to local folders.

1. First, in a new SSIS project, drag and drop the ZS Secure FTP Task. This task will let you send and receive files in FTP:
   

   Using SSIS SFTP Task in the control Flow Designer

2. Secondly, double click the ZS Secure FTP Task and select the Download FTP server file(s) to local directory. This option will download files. In Path AccessMode we can use Direct to write the path directly or to use an SSIS variable. In path, you can use “/ ” to specify the root folder /source would be a folder named source in the root. If there were another folder inside source named child1, the path would be /source/child1.  There is a nice option that you can check to include folders and child folders. If you need to include them, check the option Recursive scan. In target, the tab can be written directly (direct) and using SSIS variables. The variables will be explained later. In path you specify the path of the target. You can throw an exception if there is a need to overwrite files and folders. Finally, in Connection, press New button to create a new connection:
   

   SSIS Secure FTP Task – Download files from FTP / SFTP Server

3. The following options are available in SSIS Secure FTP Task:
   

   Option to upload, download files in SSIS

4. You can also overwrite files and folder, skip all, overwrite if source is older, overwrite if the file size is different, rename files and more:
   

   Overwrite files in SSIS

Configuring the SSIS SFTP Connection Manager

In this new section, we will show how to configure the Connection Manager. We configure before the Secure FTP task, and we pressed the New button to create a new connection.

1. In the connection manager for SFTP, we will work with the SFTP Connection Manager. We will choose if the protocol is FTP, FTPS or SFTP. Also, we need to specify the host. In this example, we are using a localhost, but in other cases, you will need to specify the IP. By default, the SFTP port is 22. If it is a different port in your case, specify the port, otherwise leave blank. The FTP default port is 21 and FTPS 990.  If you have multiple files, make sure to check the option Retain the same connection.
   

   SSIS SFTP Connection manager (Support for Classic FTP, SFTP, FTPS (i.e. FTP / SSL))

2. There are different types of Logon Types. A username and a password is the most common way to login, but you can also use other types like the Public/private key authentication, X509 certificate authentication, KerversosV5 user/password/domain, NTLM single sign-on (GSSAPI) and NTLM user/password/domain (GSSAPI).
   

   SFTP Connection Manager – Logon types (i.e. Username/password, Public / Private Key, NTLM )

3. If everything is OK, it will copy all the files from the SFTP in the source folder in the C:\destination folder.

Working with advanced filters

In order to understand filters, we will create an example for you. This time we will create SSIS SFTP task example to show how to exclude some files from the downloading process.

1. In addition, it is also possible to add advanced filters to the SSIS SFTP task to exclude or include files and folders. The following example shows how to use the Advanced filters. In the Secure FTP Task, go to the Advanced Filter page and in Exclude RegX we will write \.exe$ to exclude the file with exe extension to be downloaded. You can use RegX (regular expressions) in the advanced filter:
   

   Advanced filters FTP

 

Working with wildcards in SSIS SFTP task (Multiple files)

In order to understand wildcards, the new example will show how to work with Wildcards will help you to work with multiple files or folder with common characteristics.

1. The following example shows how to copy all the files will all the extensions in the source folder. We use the *.*, which means that any file name with any extension will be downloaded:
   

   SSIS SFTP connection manager

2. If we want to download all the files with extension txt, use the following Path:
   
   AmigaDOS

   /source/*.txt;

   1	/source/*.txt;

3. Another example would be to download all the files with the following names: file1.txt, file2.txt and file3.txt.  We could use the following Path:
   
   /source/file?.txt

   1	/source/file?.txt

 

Working with SSIS expressions and variables in SFTP / FTPS to delete FTP files

In order to understand expressions, the following example will show how to work with SSIS expressions combined with variables in the SSIS SFTP task. In this example we will delete files in SFTP using variables and expressions.

1.  First, we will first create a variable in SSIS. In the SSIS Project, go to the menu and select SSIS and variables. Create a variable with the Data type string and specify a Value. In this example the file name is MyFile and the Value file1.
   

   Create an SSIS variable

2. As a second step, we will write in path the value \destination\{{User.MyFile}}.txt. This will concatenate the folder destination with the variable MyFile. The task will delete the file1.txt from the SFTP folder.
   

   Create SSIS expressions

3. The next example will show how to create a SFTP file using expressions and variables in SSIS:
   

   Create file ftp

4. Content to write is the content of the file. In Target, go to path and write the following: For more information on placeholders check this post and also check this post.
   
   AmigaDOS

   /destination/{{User::FileName,MM-dd-yyyy}}

   1	/destination/{{User::FileName,MM-dd-yyyy}}

5. We are using a variable in SSIS named FileName of type DateTime. We are concatenating the SFTP folder destination with the variable. As you can see, the expressions with ZappySys are very simple.

Example working with variables in the SSIS FTP Task to upload files

In order to understand variables, this example about SSIS TASKS FOR SFTP will show how to work with variables. We will specify the path in a variable.

1. In this example, we will create a variable with a path. The Data type will be string and the Value will contain the path:
   

   SSIS variable values

2.  Secondly, we will go to Path AccessMode, we will select Variable and select the variable in Path variable:
   

   ssis upload files SFTP

Working with variables to create a file in FTP

In order to understand the use of variables, will show how to create a file in SFTP. In this new SSIS SFTP task example, we will create a new file using variables.

1. First, we will create an SSIS variable named Content that will store the file content. Create a variable of type String and write some text in it.
2. Secondly, in AccessMode, select Content Variable and select Create new FTP file.  In target path, you need to specify the file name that will be created. Next, select the User::Content variable to add content to the file:
   

   Example for SSIS SFTP task

Example to verify if file exists in SSIS SFTP task

Finally, we will verify if a file in SFTP exists or not using the SSIS SFTP task. The new SSIS SFTP task example will show how to work with variables and another useful ZappySys task used to get values of variables.

1. In order to start, we will use the SSIS variables, create a variable of type string named FileExists. In Storage Action, select Get FTP file exist status. In path, select the path of the file to verify if it exists or not.
   

   Verify if file exists in SFTP

2. Secondly, we will use the ZS Logging Task to check the value of the variable File Exists. This task will show the value of the variable in the log. Drag and drop the task and join it with the ZS Secure FTP Task:
   

   The SSIS Log task

3. In order to get the value, we will insert it in the Logging Task. To do that, insert the Variable and select the FileExists variable:
   

   Add the variable in the task logging

4. Finally, run the package and verify the Output (you can use View and Output to see the output) and check the value of the Logging Task. If the file exists, the value will be true. If it does not exist, the value will be false.
   

   Verify the variable in Output

How to loop through SFTP / FTP files in SSIS

You can also loop through files to perform custom action file by by. SFTP Task support Action to get File List as ADO.net Recordset

To get File list from FTP Server perform the following steps.

1. Download and Install SSIS PowerPack from here
2. Open Visual Studio and Create New SSIS Project.
3. In the SSIS control flow designer drag & drop ZS Secure FTP Task
4. Double click to configure SSIS SFTP Task.
5. From Storage Action select Get FTP files as ADO.net DataTable
   
   

   SSIS SFTP Task – Loop through Files / Folders stored on FTP Server

    

    

6. Create new FTP Connection by clicking New next to connection dropdown.
7. Select Source Path (e.g  \myroot\folder\*.txt  )
8. Select or create new SSIS Variable (e.g. varFiles ) which will hold file list result (Must be Object datatype variable).
9. Click OK to close.
10. Create 3 more SSIS variables to hold File Path (String), Name (String) and Size (Int32). We will use in the next step.
11. Now drag SSIS ForEach Loop Container Task from SSIS Toolbox
12. Edit Loop Task and Select Foreach ADO Enumerator from the Enumerator dropdown
13. Select variable (e.g. varFiles)
    
    

    Loop through files in SSIS using ForEach Loop Task (use ADO.net Recordset variable)

14. Click on Variable Mappings Tab and configure like below.
    

    Variable Mappings for ForEach Loop Task – Loop through files in a folder using SSIS

15. Drag ZS Logging Task and place inside Loop and Display variables
16. Thats

For more information to loop through files on local machine using SSIS check this article.

 

Algorithms / Ciphers supported by Secure FTP Task

Secure FTP Task comes with many option to tweak many aspects about security so you can turn on and off algorithms used by components.

Click here to learn more about supported Ciphers / Macs and Encryption Algorithms used by SSIS SFTP Task.

Conclusion

To conclude, we can say that working with SFTP is now very simple. In this article, we looked at SSIS SFTP task examples on how to upload, download, delete files and folders. We used variables, wildcards and expressions. If you liked the tasks you can start using them by downloading SSIS PowerPack from our web site here. We saw different SSIS SFTP task examples to understand how to use the task in different scenarios.

Thanks for your time.

References

• About SSIS PowerPack
• Integration Services (SSIS) Expressions

Keywords: SSIS SFTP task example,upload SFTP files, download SFTP files.