SFTP Task | CozyRoc

Overview

SFTP Task is SSIS Control Flow task for secure FTP communication. Starting from SSIS+ 1.3, the task does include support for FTPS Connection Manager.

Note:

1. If you have issues running the SFTP Task under SQL Server Agent Job, please check SSH Connection Manager FAQ.
2. SFTP Task currently doesn't provide ASCII transfer mode. You may check the Find And Replace script for a workaround. It will help you accomplish the needed result. Note: SSIS+ (1.5) now includes option for ASCII/Binary transfer mode.

Demonstration

Demonstration Video

Setup

Use the General page of the SFTP Task Editor dialog to configure the options needed to send or receive files from a secure FTP server and to manage directories and files.

Options

Name: Specify task name.

Description: Specify task description.

StopOnFailure: Specify whether the SFTP Task terminates if command fails.

Action

Specify task action. This property has the options listed in the following table.

Value	Description
Send files	Send files. Selecting this value displays the dynamic options IsLocalVariable, Local, LocalVariable, IsLocalFilterVariable (1.5 SR-2), LocalFilter (1.5 SR-2), LocalFilterVariable (1.5 SR-2), FtpConnection, IsRemoteVariable, Remote, RemoteVariable, OverwriteRemote. Starting from (1.3 SR-3) you can enter multiple local specifications, each one separated with vertical bar (\|).
Receive files	Receive files. Selecting this value display the dynamic options IsLocalVariable, Local, LocalVariable, OverwriteLocal FtpConnection, IsRemoteVariable, Remote, RemoteVariable, IncludeSubfolders (1.5), IsRemoteFilterVariable (1.5), RemoteFilter (1.5), RemoteFilterVariable (1.5).
Create remote directory	Create a remote directory. Selecting this value display the dynamic options FtpConnection, IsRemoteVariable, Remote, RemoteVariable.
Remove remote directory	Remove a remote directory. Selecting this value display the dynamic options FtpConnection, IsRemoteVariable, Remote, RemoteVariable.
Delete remote files	Delete remote files. Selecting this value display the dynamic options FtpConnection, IsRemoteVariable, Remote, RemoteVariable, IncludeSubfolders (1.5), IsRemoteFilterVariable (1.5), RemoteFilter (1.5), RemoteFilterVariable (1.5).
Rename remote file	Rename a remote file. Selecting this value display the dynamic options FtpConnection, IsOldNameVariable, OldName, OldNameVariable, IsNewNameVariable, NewName, NewNameVariable.
Get remote files list (1.2 SR-1)	Get list of remote files. Selecting this value display the dynamic options ResultVariable, FtpConnection, IsRemoteVariable, Remote, RemoteVariable, IncludeSubfolders (1.5), IsRemoteFilterVariable (1.5), RemoteFilter (1.5), RemoteFilterVariable (1.5).

IsLocalVariable

Indicates whether the local path is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The local path is stored in a variable. Selecting the value displays the dynamic option LocalVariable.
False	The local path is specified in a File connection manager. Selecting the value displays the dynamic option Local.

Local

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager Editor

LocalVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

IsLocalFilterVariable (1.5 SR-2)

Indicates whether the local filter is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The local filter is stored in a variable. Selecting the value displays the dynamic option LocalFilterVariable.
False	The local filter is explicitly specified. Selecting the value displays the dynamic option LocalFilter.

LocalFilter (1.5 SR-2)

Specify local files filter criteria. The criteria is specified using FLEE expression. You can use the following elements from System.IO FileInfo class: CreationTime, Directory, DirectoryName, Extension, FullName, IsReadOnly, LastAccessTime, LastWriteTime, Length, Name.

For example to get all zip files from the past 5 days use the following expression: LastWriteTime > Now.AddDays(-5) and Name.EndsWith(".zip")

LocalFilterVariable (1.5 SR-2)

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

FtpConnection

Select an existing SSH connection manager or FTPS connection manager (1.3), or click <New connection...> to create a connection manager.

Related Topics: SSH Connection Manager, FTPS Connection Manager

IsRemoteVariable

Indicates whether the remote path is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The remote path is stored in a variable. Selecting the value displays the dynamic option RemoteVariable.
False	The remote path is explicitly specified on the remote server. Selecting the value displays the dynamic option Remote.

Remote: Select remote path on the remote server.

RemoteVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.
Starting from (1.3 SR-3) you can enter array of items for remote parameter (string or IFileInfo). The variable type must be Object.

Related Topics: Integration Services Variables, Add Variable

OverwriteRemote

Specify whether remote file can be overwritten.

Remark(s):
If you are sending 0-length files, make sure this option is set to True. Otherwise the remote file would not be created.

OverwriteLocal: Specify whether local file can be overwritten.

IsOldNameVariable

Indicates whether the old name is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The old name is stored in a variable. Selecting the value displays the dynamic option OldNameVariable.
False	The old name is specified in a File connection manager. Selecting the value displays the dynamic option OldName.

OldName

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

OldNameVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

IsNewNameVariable

Indicates whether the new name is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The new name is stored in a variable. Selecting the value displays the dynamic option NewNameVariable.
False	The new name is specified in a File connection manager. Selecting the value displays the dynamic option NewName.

NewName

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

NewNameVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

ResultVariable (1.2 SR-1)

Select an existing user-defined variable, or click <New variable...> to create a variable. Check also ExtendedRemoteFileInfo option below.

Related Topics: Integration Services Variables, Add Variable

ExtendedRemoteFileInfo (1.2 SR-2)

Indicates whether ResultVariable contains array of file name or extended file information. This property has the options listed in the following table.

Value	Description
True	The ResultVariable contains array of IFileInfo interface objects.
False	The ResultVariable contains array of file names.

Remark(s):
Not visible in the task user interface.

Check following script how to access ExtendedRemoteFileInfo ResultVariable (you have to reference CozyRoc.SSIS library in your script project):

Imports CozyRoc.SqlServer.SSIS

...

Dim fis As Object()
Dim vars As Variables
Call Dts.VariableDispenser.LockOneForRead("ResultVar", vars)
Try
    fis = CType(vars("ResultVar").Value, Object())
Finally
    Call vars.Unlock()
End Try

Dim firstInfo As IFileInfo = CType(fis(0), IFileInfo)

IsRemoteWildcard (1.2 SR-2)

Indicates whether the remote path is a wildcard. This property has the options listed in the following table.

Value	Description
True	The remote path is a wildcard.
False	The remote path is explicit specification.

Remark(s):
Not visible in the task user interface.

NoRemoteFilesFail (1.3 SR-5)

Specifies if the task fails when specified remote files doesn't exist. This property has the options listed in the following table.

Value	Description
True	The task fails if specified remote files doesn't exist.
False	The task ignores and completes successfully if specified remote files doesn't exist.

Remark(s):
Not visible in the task user interface.

IncludeSubfolders (1.5)

Specifies if remote files are recursively included. This property has the options listed in the following table.

Value	Description
True	The task includes all remote files recursively, starting from the specified remote path.
False	The task includes only the specified remote path files. This is the default option.

IsRemoteFilterVariable (1.5)

Indicates whether the remote filter is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The remote filter is stored in a variable. Selecting the value displays the dynamic option RemoteFilterVariable.
False	The remote filter is explicitly specified. Selecting the value displays the dynamic option RemoteFilter.

RemoteFilter (1.5)

Specify remote files filter criteria. The criteria is specified using FLEE expression. You can use the following elements: Name, FullName, Size, ModifiedTime, IsDirectory.

For example to get all zip files from the past 5 days use the following expression: Modifiedtime > Now.AddDays(-5) and Name.EndsWith(".zip")

RemoteFilterVariable (1.5)

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

Data streaming

If you use the task to stream data, set RetainSameConnection property to TRUE on the connection manager. Otherwise the connection will be closed before all data is transferred.

Samples

For a sample how to process and iterate over remote file information, check this package.
For a sample how to stream data without intermediate storage, check this package.
For a sample how create data in-memory in data flow and stream without intermediate storage, check this package.
To read and process remote file information inside data flow, check this script.