SFTP Task

Overview

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

Note:

1. If you have issues running the SFTP Task under the SQL Server Agent Job, please check the SSH Connection Manager FAQ.
2. Starting with SSIS+ (1.5), ASCII transfer mode is supported. You may check the Find And Replace script for a workaround for older versions. It will help you accomplish the result you desire.

Demonstration

Setup

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

Parameters

Name

Specify the task name.

Description

Specify the task description.

StopOnFailure

Specify whether or not the SFTP Task terminates if the command fails.

Action

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

Option Description
Send files Sends files. Selecting this action displays the dynamic parameters 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 a vertical bar (|).
Receive files Receives files. Selecting this action displays the dynamic parameters IsLocalVariable, Local, LocalVariable, OverwriteLocal FtpConnection, IsRemoteVariable, Remote, RemoteVariable, IncludeSubfolders (1.5), IsRemoteFilterVariable (1.5), RemoteFilter (1.5), RemoteFilterVariable (1.5).
Create remote directory Creates a remote directory. Selecting this action displays the dynamic parameters FtpConnection, IsRemoteVariable, Remote, RemoteVariable.
Remove remote directory Removes a remote directory. Selecting this action displays the dynamic parameters FtpConnection, IsRemoteVariable, Remote, RemoteVariable.
Delete remote files Deletes remote files. Selecting this action displays the dynamic parameters FtpConnection, IsRemoteVariable, Remote, RemoteVariable, IncludeSubfolders (1.5), IsRemoteFilterVariable (1.5), RemoteFilter (1.5), RemoteFilterVariable (1.5).
Rename remote file Renames a remote file. Selecting this action displays the dynamic parameters FtpConnection, IsOldNameVariable, OldName, OldNameVariable, IsNewNameVariable, NewName, NewNameVariable.
Get remote files list (1.2 SR-1) Gets a list of remote files. Selecting this action displays the dynamic parameters ResultVariable, FtpConnection, IsRemoteVariable, Remote, RemoteVariable, IncludeSubfolders (1.5), IsRemoteFilterVariable (1.5), RemoteFilter (1.5), RemoteFilterVariable (1.5).
IsLocalVariable

Indicate whether or not the local path is stored in a variable. This parameter has the options listed in the following table.

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

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

Related topics: File Connection Manager Editor

LocalVariable

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

Related Topics: Integration Services Variables, Add Variable

IsLocalFilterVariable (1.5 SR-2)

Indicate whether or not the local filter is stored in a variable. This parameter has the options listed in the following table.

Options Description
True The local filter is stored in a variable. Selecting this option displays the dynamic parameter LocalFilterVariable.
False The local filter is explicitly specified. Selecting this option displays the dynamic parameter 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 new 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 new connection manager.

Related Topics: SSH Connection Manager, FTPS Connection Manager

IsRemoteVariable

Indicate whether or not the remote path is stored in a variable. This parameter has the options listed in the following table.

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

Select remote path on the remote server.

RemoteVariable

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

Related Topics: Integration Services Variables, Add Variable

OverwriteRemote

Specify whether or not a 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 will not be created.

OverwriteLocal

Specify whether or not the local file can be overwritten.

IsOldNameVariable

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

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

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

Related topics: File Connection Manager

OldNameVariable

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

Related Topics: Integration Services Variables, Add Variable

IsNewNameVariable

Indicate whether or not the new name is stored in a variable. This parameter has the options listed in the following table.

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

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

Related topics: File Connection Manager

NewNameVariable

Select an existing user-defined variable, or click <New variable...> to create a new 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 new variable. Also check the ExtendedRemoteFileInfo parameter below.

Related Topics: Integration Services Variables, Add Variable

ExtendedRemoteFileInfo (1.2 SR-2)

Indicate whether or not ResultVariable contains an array of file names or extended file information. This parameter has the options listed in the following table.

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

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

Check the following script showing how to access ExtendedRemoteFileInfo ResultVariable (you must reference the 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)

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

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

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

NoRemoteFilesFail (1.3 SR-5)

Specify if the task should fail when the specified remote files don't exist. This parameter has the options listed in the following table.

Options Description
True The task fails if the specified remote files don't exist.
False The task ignores and completes successfully if the specified remote files don't exist.

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

IncludeSubfolders (1.5)

Specify if remote files are recursively included. This parameter has the options listed in the following table.

Options 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)

Indicate whether or not the remote filter is stored in a variable. This parameter has the options listed in the following table.

Options Description
True The remote filter is stored in a variable. Selecting this option displays the dynamic parameter RemoteFilterVariable.
False The remote filter is explicitly specified. Selecting this option displays the dynamic parameter 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 new variable.

Related Topics: Integration Services Variables, Add Variable

RemotePathSeparator (1.5 SR-4)

Specify remote path separator. The default separator is backslash (/).

Data streaming

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

Related scripts

Samples

  • For a sample showing how to process and iterate over remote file information, look at this package.
  • For a sample showing how to stream data without intermediate storage, look at this package.
  • For a sample showing how to create data in-memory in the data flow and stream without intermediate storage, look at this package.