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.

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, FtpConnection, IsRemoteVariable, Remote, RemoteVariable, OverwriteRemote.
Receive files	Receive files. Selecting this value display the dynamic options IsLocalVariable, Local, LocalVariable, OverwriteLocal FtpConnection, IsRemoteVariable, Remote, RemoteVariable.
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.
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.

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

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.

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.