Help > Tasks > SFTP Task >
SSIS SFTP Task (Secure FTP, SFTP, FTPS)
 PreviousNext

SSIS SFTP Task can perform many operations using SFTP, FTP/SSL (i.e. FTPS) or Classic FTP Protocol. It supports many advanced options and various authentication mechanism (e.g. NTLM, Kerberos, Client Certificate, Private Key) .

Download SSIS PowerPack

Content

Video Tutorial

Step-By-Step

In this tutorial we are going to learn how to use ZS Secure FTP/SFTP Task for Create, Upload, Download and Delete files even more. We are going to use local FTP Server, Here, you can also Download and Install Rebex Tiny SFTP Server.
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. In order to connect Secure SFTP server in SSIS, you will need obtained FTP/SFTP Credential.
  3. Once you have your Credential you may proceed to create new SSIS package. In BIDS/SSDT create new SSIS package Project
  4. Very first thing you need to do to connect to FTP server is configure SSIS connection manager.
    SFTP Task Operations - Connection
  5. Select ZS-SFTP Connection Manager from the Connection Managers list and Click on Add Button.
    SFTP Task Operations - Connection
  6. Now in Connection Manager UI, Select SFTP - SSH File Transfer Protocol and Enter your Host name and Select Logon Type Username and Password and Enter it, and Click Test Connection. If test successful then hit OK to close the connection manager dialogbox.
    SFTP Task Operations - Connection
  7. Here, we will showing you how to Create FTP Folder & File, Download Files, Upload Files and Delete FTP Files even Loop through using SSIS ZS Secure FTP Task (SFTP, FTP, FTPS) Properties. Now, you need to create Design like below image. Just Drag and Drop Components from SSIS Toolbox.
    SFTP Task Operations - Upload, Download, Delete
  8. We need Variables to Execute this task.
  9. Let's Create Variables, In Visual Studio Right Click on Design Panel, Select Variables.
    SFTP Task Operations - Create Variable
    Now, Create Variable with DataType, Same like below Image. Enter Source Path and Target Path in Value. 
    Note: Here is Example of Local and Server Path.

Examples:
c:\data\file123.csv (Local file Path)
/download/file123.csv (Server file Path)
*.csv (all files with .csv Extension using Wildcard Pattern)
    SFTP Task Operations - Create Variable

How to Download files from FTP Server with just few clicks.

  1. Double Click on ZS Secure FTP Task to configure it for download files from server.
  2. Select Storage ActionDownload FTP Server file(s) to local directory. Select Source Connection and Select folder or file you want to download from FTP Server. Set Variable Path and Select Variable varTargetPath we have created.
    ssis-sftp-multi-file-download-pattern-search
  3. Click OK to save ZS Secure FTP Task - Download Files from FTP UI settings.

How to Upload files from Local to FTP Server with just few clicks.

  1. Double Click on ZS Secure FTP Task to configure it.
  2. Set Storage Action to Upload local file(s) to FTP server and Variable Path and Select Variable varSourcePath we have already create. Select Target Connection and Select FTP Folder where you want to upload it.
    ssis-sftp-multi-file-upload-pattern-search
  3. Thats all, Hit on OK button and Execute Task.

How to create New File/Folder in FTP Server using ZS Secure FTP Task (SFTP, FTP, FTPS)

  1. Double Click on ZS Secure FTP Task to configure it.
  2. Set Storage Action to Create new FTP file. Follow second number step and you can Edit content easily. Select Target Connection and Select FTP Folder and give file name with suitable extension.
    ssis-sftp-ftp-create-new-file-write-content
  3. Click OK to save ZS Secure FTP Task - Create New Files UI settings and Run or Execute your Task.
  4. Here, We can create New Folder too in FTP Server using ZS Secure FTP Task (SFTP, FTP, FTPS) follow below image steps.
    ssis-sftp-ftp-create-new-file-write-content
  5. We have Advanced Filter Tab for RegX. For more information check our latest Blog for Regular Expression.
    ssis-sftp-upload-include-exclude-regex-pattern-use-wildcard
  6. Thats all, Hit on OK button and Execute Task.

How to loop through get FTP file list from FTP Server.

  1. Double Click on ZS Secure FTP Task to configure it.
  2. Set Storage Action to Get FTP file list as ADO .net DataTable. Select Source Path from FTP Server and use Wildcard pattern to get all the file with same extension. Select Result Variable with Object DataType for file list.
    ssis-sftp-loop-ftp-files-using-foreach-task
  3. Click on OK button to save Secure FTP Task configure setting UI.
  4. Double click on Foreach Loop Container to configure it.
  5. In the Collection Tab from left panel set Enumerator to Foreach ADO Enumerator and select ADO object source variable we have created.
    ssis-sftp-loop-ftp-files-using-foreach-task
  6. Now, click on Variable Mappings and Map foreach loop task variables to correct index, configure it with below image steps. If you just need File Name then map #1 Index.
    ssis-sftp-loop-ftp-files-using-foreach-task
  7. Click on OK button to save Foreach Loop Task configure setting UI.
  8. Now, double click on ZS Logging Task (Log File Info) for configure it, For more Information you can click here.
  9. Set Log Mode to MessageBox to see Result in Message Box and Insert Variable for file name.
    ssis-sftp-loop-ftp-files-using-foreach-task - Logging Task
  10. Click on OK button to save Logging Task configure setting UI.

How to Delete Files in FTP Server using ZS Secure FTP Task (SFTP, FTP, FTPS)

  1. Double Click on ZS Secure FTP Task to configure it.
  2. Set Storage Action to Delete FTP file(s). Select FTP Connection and Variable as path. Select variable for path variable we have created. Last but not the least, We can Delete Files from FTP Server.
    ssis-sftp-ftp-delete-files
  3. Click on OK button to save Secure FTP Task configure setting UI.
  4. Thats all, For Execute Package right click on your Package and Select Execute Package.
    ssis-sftp-ftp-delete-files

Properties

Property Name Description
UsePartialExtWhileTransfer Use .partial extension for during transfer (e.g. data.zip.partial). After Upload/download is done file is renamed back to the original name (e.g. data.zip). This option is useful when you have a consumer process which sometimes starts reading partial file while you are in the middle of uploading or downloading. This can create an issue of reading partial file. Enabling this option ensures that file is only available by its real name once its fully transferred.
KeepPartialFile
StorageAction Specifies what storage action you want to perform

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
DownloadFileFromFtp [10] Download FTP server file(s) to local directory
UploadFileToFtp [20] Upload local file(s) to FTP server
DeleteFtpFile [30] Delete FTP file(s)
DeleteFtpFolder [40] Delete FTP folder(s)
MoveFtpFileToFtp [60] Move FTP file(s)
RenameFtpFile [70] Rename FTP file(s)
CreateNewFtpFile [90] Create new FTP file
CreateFtpFolder [100] Create new FTP folder
CreateFtpFolderIfNotExists [111] Create new FTP folder if does not exist
GetFtpFileSize [1010] Get FTP file(s) size
GetFtpFileExistsFlag [1030] Get FTP file exist status
GetFtpFileCount [1040] Get FTP files count
GetFtpFileLastModifiedDate [1050] Get FTP file LastModifiedDate
GetFtpFileCreationDate [1051] Get FTP file CreationDate
GetFtpFileLastAccessDate [1052] Get FTP file LastAccessDate (Last Read)
GetFtpFilePathLatest [1053] Get Latest file path (Sort By LastModifiedDate)
GetFtpFilePathOldest [1054] Get Oldest file path (Sort By LastModifiedDate)
GetFtpFileDataAsString [1080] Read FTP File content as text
GetFtpFileDataAsBinary [1081] Read FTP File content as byte array
GetFtpFolderExistsFlag [1090] Get FTP folder exist status
GetFtpFileListAsADONETDataTable [1100] Get FTP file list as ADO.net DataTable
GetFtpFolderListAsADONETDataTable [1110] Get FTP folder list as ADO.net DataTable
FileOverwriteAction Specifies how to handle overwrite action

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
ThrowException [0] ThrowException
SkipAll [1] SkipAll
OverwriteAll [2] OverwriteAll
OverwriteOlder [3] OverwriteOlder
OverwriteDifferentSize [4] OverwriteDifferentSize
ResumeIfPossible [5] ResumeIfPossible
Rename [6] Rename
OverwriteDifferentChecksum [7] OverwriteDifferentChecksum
TreatRegXForFullPath Treat include/exclude Regular Expression as Full Path expression rather than file name. If you have a use case of checking against Folder name pattern along with file name then use this option. When you check this option then using ^ in the front of expression will not work for File name pattern so instead of something like this ^MyFile\w+\.csv$ you need to change prefix check like this  [\\|/]MyFile\w+\.csv$ this way it works against full path check.
OverWriteOption

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
AlwaysOverwrite [0] AlwaysOverwrite
FailIfExists [1] FailIfExists
SkipIfExists [2] SkipIfExists
AutoSetContentType
UserDefinedContentTypes
DefaultContentType
MetadataKeyValue
TagsKeyValue
MetadataKeyValueList
TagsKeyValueList
EnableMetadataUpdate
EnableTagsUpdate
MaxConcurrentTransfers
ContinueOnError This option prevents task failure on error such as transfer of file(s) failed for some reason, delete operation failed for few files.
ThrowErrorIfSourceNotFound Throw error if source file/folder is missing
DeleteSourceWhenDone Delete source file after file is transferred successfully
SourceStorageConnection Connection for Storage account
SourcePathAccessMode

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Direct [0] Direct
Variable [1] Variable
Connection [2] Connection
SourcePathValue Specifies storage source
SourcePathConnection Specifies storage source
SourcePathVariable Specifies storage source
Recursive Specifies how to handle scanning of items
ResultVariable
TargetStorageConnection Connection for Storage account
TargetPathAccessMode

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Direct [0] Direct
Variable [1] Variable
Connection [2] Connection
TargetPathValue Specifies storage target
TargetPathConnection Specifies storage target
TargetPathVariable Specifies storage target
CreateMissingTargetFolder Specify this option if you want to create target folder automatically if its missing
ExcludeRegXPattern Regular expression pattern to exclude items from selection  (if you apply MyFile*.* filter for source path and then use ExcludeRegXPattern=(\.msi$|\.exe$) then it will include all files with matching name but exclude *.msi and *.exe
IncludeRegXPattern Regular expression pattern to include items from selection (if you apply MyFile*.* filter for source path and then use IncludeRegXPattern=(\.txt$|\.csv$) then it will include only txt and csv files with matching name pattern
SortBy Property by which you want to sort result

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Size [0] File Size in Bytes
LastModifiedDate [1] File Last Modified DateTime
CreationDate [2] File Creation DateTime
AgeInDays [3] File Age in Days
LastEditInDays [4] Last Edit in Days
Content [5] File Content
Exists [6] File Exists Flag
FileCount [7] File Count
FolderPath [8] File Directory Path
FilePath [9] File Path
FileName [10] File Name
FileExtension [11] File Extension
FileEncoding [12] File Encoding
IsLocked [13] Is File Locked
SortDirection Sort order (e.g. Ascending or Descending)

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Asc [0] Asc
Desc [1] Desc
WhereClause Where clause expression to filter items. (e.g. Size>100 and Extension IN ('.txt','.csv') )
EnableSort Sort items by specified attribute
MaxItems Maximum items to return (e.g. TOP)
SkipItems Maximum items to skip (e.g. SKIP)
LoggingMode

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Normal [0] Normal
Medium [1] Medium
Detailed [2] Detailed
Debugging [3] Debugging
PrefixTimestamp When you enable this property it will prefix timestamp before Log messages.

Setting UI

ssis-sftp-ftp-delete-files - Setting UI
ssis-sftp-ftp-delete-files - Setting UI

See Also

Articles / Tutorials

Click here to see all articles for [SSIS SFTP Task] category
SSIS Data Load €“ SQL Server to FTP/SFTP (Split Files, GZip)

SSIS Data Load €“ SQL Server to FTP/SFTP (Split Files, GZip)

Introduction In this blog post you will see how easy it is to load large amount of data from SQL Server to FTP/SFTP. SSIS Data Load €“ SQL Server to FTP/SFTP (Split Files, GZip). For demo purpose we will use SQL Server as relational source but you can use same steps for any database engine […]

Read SFTP / FTP Files in SSIS (CSV, JSON, XML)

Read SFTP / FTP Files in SSIS (CSV, JSON, XML)

Introduction In our previous blog, we saw how to perform SFTP / FTP File Operations in SSIS. Now in this blog, we will see How to Read SFTP / FTP Files in SSIS (CSV, JSON, XML Format files). To illustrate, we will use ZappySys SSIS PowerPack, which includes several tasks to import/export data from multiples sources […]

Download latest File from FTP using SSIS (SFTP / FTPS)

Download latest File from FTP using SSIS (SFTP / FTPS)

Introduction In this blog, we will learn how to Download Latest File from FTP using SSIS Secure FTP Task, We also learn how to connect with FTP/SFTP and also see how to Get Latest File list in ADO.net DataTable variable and How to sort ADO.net DataTable and get and set latest FTP File Path in a variable […]

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

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 […]

Using Regular Expressions in SSIS

Using Regular Expressions in SSIS

Introduction In this short article you will learn how to write Regular expressions in SSIS (i.e. Regex) and what tool to use to test them. You will also find helpful resources on how to write more sophisticated expressions and learn more about them. For demo purpose we will use FREE SSIS Regex Parser Task to parse and […]

Copyrights reserved. ZappySys LLC.