Overview
This task replaced the SFTP Task as of SSIS+ 1.7. The name was changed because it can do much more than just work with files on an SFTP server. This task can be used to manage files on a remote server and to transfer files from or to a remote server. The remote connection may be any of the following:
- SSH
- FTPS
- Salesforce
- Sharepoint
- REST - since 2.1 the supported configurations (Amazon S3, Dropbox, Google Cloud Storage, Google Storage, Hubspot, MailChimp, Marketo, Microsoft Graph) can be initialized directly from the "Connection" dropdown.
To use the task for secure transfers, use an SSH or FTPS connection manager for the remote connection. Use SSH if connecting with an SSH Server (SFTP server). Use FTPS if connecting with an FTP server. The FTPS connection offers four different types of secure connections (ExplicitTLS, ImplicitTLS, ExplicitSSL and ImplicitSSL) as well as an unsecure connection type.
Most of the setup is accomplished under the “General” tab. The Action parameter provides several options:
-
Parameters Description Local, IsLocalVariable, LocalVariable These parameters are used to specify the path for the files which are to be sent. They are files on the local server, which is the server where the SSIS package is executed.
Local is used to specify the connection manager which contains the path for the files to be sent.
If you prefer to use a variable so that the path can be modified at runtime, then set IsLocalVariable to True and load a variable into LocalVariable to specify the path. Click on the orange “Expand Options” button to display these parameters.
LocalFilter, IsLocalFilterVariable, LocalVariableFilter These parameters are used to specify criteria to be applied against the files to be sent.
Use the LocalFilter parameter to specify the criteria using a FLEE expression. You can use the following elements from System.IO FileInfo class: CreationTime, Directory, DirectoryName, Extension, FullName, IsReadOnly, LastAccessTime, LastWriteTime, Length, Name.
If you prefer to use a variable so that the filter can be modified at runtime, then set IsLocalFilterVariable to True and load a variable into LocalVariableFilter to specify the filter criteria. Click on the orange “Expand Options” button to display these parameters.
Remote, IsRemoteVariable, RemoteVariable These parameters specify where files being transferred to the remote server should be placed. The remote server is the server which is remote from the server where the SSIS package is being executed.
Use the Remote parameter to specify the path on the remote server where the files are to be placed.
If you prefer to use a variable so that the path can be modified at runtime, then set IsRemoteVariable to True and load a variable into RemoteVariable to specify the path. You can enter an array of items for a remote parameter (string or IFileInfo.) The variable type must be Object. Click on the orange “Expand Options” button to display these parameters.
OverwriteRemote The OverwriteRemote parameter is used to indicate whether a file with the same name may be overwritten or not on the remote server.
NoFilesFail This parameter is used to indicate whether or not to fail if there are no files to send to the remote server.
Connection Use the Connection parameter to specify the connection manager for the remote server. It may be an FTPS, REST, Sharepoint, or SSH connection manager.
IncludeSubfolders Use this parameter to specify if local files are recursively included.
-
Parameters Description Remote, IsRemoteVariable, RemoteVariable These parameters specify where the files being transferred from the remote server are located. The remote server is the server which is remote from the server where the SSIS package is being executed.
Use the Remote parameter to specify the path on the remote server where the files are located.
If you prefer to use a variable so that the path can be modified at runtime, then set IsRemoteVariable to True and load a variable into RemoteVariable to specify the path. You can enter an array of items for a remote parameter (string or IFileInfo.) The variable type must be Object. Click on the orange “Expand Options” button to display these parameters.
RemoteFilter, IsRemoteFilterVariable, RemoteFilterVariable These parameters are used to specify criteria to be applied against the files to be received.
Use the RemoteFilter parameter to specify the criteria using a FLEE expression. You can use the following elements from the IFileInfo interface: Name, FullName, Size, ModifiedTime, IsDirectory.
If you prefer to use a variable so that the filter can be modified at runtime, then set IsRemoteFilterVariable to True and load a variable into RemoteVariableFilter to specify the filter criteria. Click on the orange “Expand Options” button to display these parameters.
IsRemoteWildcard This parameter is used to Indicate whether or not the remote path is a wildcard.
This parameter is not available in the user interface for the File Transfer Task and can only be accessed by opening the Properties for the Task.
IncludeSubfolders Use this parameter to specify if remote files are recursively included.
Connection Use the Connection parameter to specify the connection manager for the remote server. It may be an FTPS, REST, Sharepoint, or SSH connection manager.
Local, IsLocalVariable, LocalVariable These parameters are used to specify the path for where to place the files which are to be received.
Local is used to specify the connection manager which contains the path for the placement of the files.
If you prefer to use a variable so that the path can be modified at runtime, then set IsLocalVariable to True and load a variable into LocalVariable to specify the path. Click on the orange “Expand Options” button to display these parameters.
OverwriteLocal Use this parameter to specify if the local files may be overwritten.
-
Parameters Description Remote, IsRemoteVariable, RemoteVariable These parameters specify where the remote directory is to be created on the remote server, as well as the name of the remote directory. The remote server is the server which is remote from the server where the SSIS package is being executed.
Use the Remote parameter to specify the path on the remote server where the directory is to be created.
If you prefer to use a variable so that the path can be modified at runtime, then set IsRemoteVariable to True and load a variable into RemoteVariable to specify the path. The variable type must be String. Click on the orange “Expand Options” button to display these parameters.
Connection Use the Connection parameter to specify the connection manager for the remote server. It may be an FTPS, REST, Sharepoint, or SSH connection manager.
-
Parameters Description Remote, IsRemoteVariable, RemoteVariable These parameters specify where the remote directory is to be removed on the remote server. The remote server is the server which is remote from the server where the SSIS package is being executed.
Use the Remote parameter to specify the path on the remote server where the directory is to be removed.
If you prefer to use a variable so that the path can be modified at runtime, then set IsRemoteVariable to True and load a variable into RemoteVariable to specify the path. The variable type must be String. Click on the orange “Expand Options” button to display these parameters.
Connection Use the Connection parameter to specify the connection manager for the remote server. It may be an FTPS, REST, Sharepoint, or SSH connection manager.
-
Parameters Description Remote, IsRemoteVariable, RemoteVariable These parameters specify the path where the remote files are to be removed from the remote server.
Use the Remote parameter to specify the path on the remote server where the files are to be removed.
If you prefer to use a variable so that the path can be modified at runtime, then set IsRemoteVariable to True and load a variable into RemoteVariable to specify the path. You can enter an array of items for a remote parameter (string or IFileInfo.) The variable type must be Object. Click on the orange “Expand Options” button to display these parameters.
RemoteFilter, IsRemoteFilterVariable, RemoteFilterVariable These parameters are used to specify criteria to be applied against the files to be deleted.
Use the RemoteFilter parameter to specify the criteria using a FLEE expression. You can use the following elements from the IFileInfo interface: Name, FullName, Size, ModifiedTime, IsDirectory.
If you prefer to use a variable so that the filter can be modified at runtime, then set IsRemoteFilterVariable to True and load a variable into RemoteVariableFilter to specify the filter criteria. Click on the orange “Expand Options” button to display these parameters.
IncludeSubfolders Use this parameter to specify if remote files are recursively deleted.
IsRemoteWildcard This parameter is used to Indicate whether or not the remote path is a wildcard.
This parameter is not available in the user interface for the File Transfer Task and can only be accessed by opening the Properties for the Task.
Connection Use the Connection parameter to specify the connection manager for the remote server. It may be an FTPS, REST, Sharepoint, or SSH connection manager.
NoFilesFail This parameter is used to indicate whether or not to fail if there are no files to delete on the remote server.
-
Parameters Description OldName, IsOldNameVariable, OldnameVariable These parameters are used to specify the name of the existing file that is to be renamed and/or moved on the remote server.
Use the OldName parameter to specify the path on the remote server where the file currently resides.
If you prefer to use a variable so that the path can be modified at runtime, then set IsOldNameVariable to True and load a variable into OldNameVariable to specify the path. Click on the orange “Expand Options” button to display these parameters.
NewName, IsNewNameVariable, NewNameVariable These parameters are used to specify the new name for the file on the remote server.
Use the NewName parameter to specify the filename and path on the remote server where the file will reside after being renamed and/or moved.
If you prefer to use a variable so that the path can be modified at runtime, then set IsNewNameVariable to True and load a variable into NewNameVariable to specify the path. Click on the orange “Expand Options” button to display these parameters.
Connection Use the Connection parameter to specify the connection manager for the remote server. It may be an FTPS, REST, Sharepoint, or SSH connection manager.
NoFilesFail This parameter is used to indicate whether or not to fail if there are no files to rename or move on the remote server.
-
Parameters Description Remote, IsRemoteVariable, RemoteVariable These parameters specify where the files of interest are located on the remote server. The remote server is the server which is remote from the server where the SSIS package is being executed.
Use the Remote parameter to specify the path on the remote server where the files are located.
If you prefer to use a variable so that the path can be modified at runtime, then set IsRemoteVariable to True and load a variable into RemoteVariable to specify the path. You can enter an array of items for a remote parameter (string or IFileInfo.) The variable type must be Object. Click on the orange “Expand Options” button to display these parameters.
RemoteFilter, IsRemoteFilterVariable, RemoteFilterVariable These parameters are used to specify criteria to be applied against the files to be included in the list.
Use the RemoteFilter parameter to specify the criteria using a FLEE expression. You can use the following elements from the IFileInfo interface: Name, FullName, Size, ModifiedTime, IsDirectory.
If you prefer to use a variable so that the filter can be modified at runtime, then set IsRemoteFilterVariable to True and load a variable into RemoteFilterVariable to specify the filter criteria. Click on the orange “Expand Options” button to display these parameters.
IsRemoteWildcard This parameter is used to Indicate whether or not the remote path is a wildcard.
This parameter is not available in the user interface for the File Transfer Task and can only be accessed by opening the Properties for the Task.
IncludeSubfolders Use this parameter to specify if remote files are recursively included.
Connection Use the Connection parameter to specify the connection manager for the remote server. It may be an FTPS, REST, Sharepoint, or SSH connection manager.
ResultVariable, ExtendedRemoteFileInfo Use the ResultVariable parameter to indicate where the list of files should be placed.
The ExtendedRemoteFileInfo parameter is not available in the user interface for the File Transfer Task and can only be accessed by opening the Properties for the Task. Use it to Indicate whether or not ResultVariable contains a string of remote file names or an array of extended file information.
NoFilesFail This parameter is used to indicate whether or not to fail if there are no files on the remote server to include in the list.
Once an Action is selected, the relevant parameters are displayed for that Action. Some of the parameters are grouped based on whether they are related to the local or the remote host.
The words “local” and “remote” are defined from the perspective of the server where the SSIS package is being executed. Accordingly, the parameters in the “Local Parameters” group refer to the server where the SSIS package is being executed and contain the word “local” in their names. The parameters in the “Remote Parameters” group refer to the server that is remote from the server where the SSIS package is being executed and contain the word “remote” in their names.
It follows that setting the Action to “Send files” means that files will be transferred from the server where the SSIS package is being executed to a remote server. Setting the parameter to “Receive files” means that files will be transferred from a remote server to the server where the SSIS package is being executed.
If you’re uploading or downloading files or a list, you’ll need to specify the local path with either a “File” connection manager or a “FlatFile” connection manager. Note that if you only want to upload or download a single file, you simply specify the full file path and filename using one of these connection managers.
Use the filter-related parameters to apply specific criteria to files. Parameters are provided for filtering on the local or the remote host, depending on the Action.
Subfolders may be included or excluded when working with files on the remote server or when sending files to the remote server. Hence, the IncludeSubfolders parameter may be used with the following Actions: Send files, Get remote files list, Delete remote files, and Receive files.
You may also specify whether files with the same name may be overwritten on the destination server (local or remote) by using the OverwriteLocal or OverwriteRemote parameters.
Files on a remote server may be moved by using the “Rename remote files” Action. Simply include the name of the directory where you want to move the file(s) in the path using the NewName parameter.
If you are not sure there will be files on the remote server when you execute the package and you do not want the package to fail if there are no files, then set the NoFilesFail parameter to "False". If you are converting your packages over from using the deprecated SFTP Task, then be aware that this is a new parameter. With the SFTP Task, setting the StopOnFailure parameter to "False" would accomplish the same thing. With the File Transfer Task, the StopOnFailure parameter governs the behavior when other errors occur, but does not affect whether the packages fails or not when there is no file.
All actions support automatic retrying when error occurs. To specify to retry only if the error message matches the RegEx pattern use the optional parameter RetryOnError. How much seconds to pause between retries is specified with the RetryPause parameter. The default is 30 seconds. The maximum number of retry attempts is specified with the Retry parameter.
Demonstration
Case Study
Parameters
General
Use the General page of the File Transfer Task Editor dialog to configure the parameters needed to send or receive files and to manage directories and files.
Specify the task name.
Specify the task description.
Specify whether or not the File Transfer Task terminates if the command fails.
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, LocalFilter, LocalFilterVariable, Connection, IsRemoteVariable, Remote, RemoteVariable, OverwriteRemote, IncludeSubfolders.
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, Connection, IsRemoteVariable, Remote, RemoteVariable, IsRemoteFilterVariable, RemoteFilter, RemoteFilterVariable, IncludeSubfolders. Create remote directory Creates a remote directory. Selecting this action displays the dynamic parameters Connection, IsRemoteVariable, Remote, RemoteVariable. Remove remote directory Removes a remote directory. Selecting this action displays the dynamic parameters Connection, IsRemoteVariable, Remote, RemoteVariable. Delete remote files Deletes remote files. Selecting this action displays the dynamic parameters Connection, IsRemoteVariable, Remote, RemoteVariable, IsRemoteFilterVariable, RemoteFilter, RemoteFilterVariable, IncludeSubfolders. Rename remote file Renames a remote file. Selecting this action displays the dynamic parameters Connection, IsOldNameVariable, OldName, OldNameVariable, IsNewNameVariable, NewName, NewNameVariable. Get remote files list Gets a list of remote files. Selecting this action displays the dynamic parameters ResultVariable, Connection, IsRemoteVariable, Remote, RemoteVariable, IsRemoteFilterVariable, RemoteFilter, RemoteFilterVariable, IncludeSubfolders. For all actions are displayed the Retry parameters - RetryOnError, RetryPause, Retry.
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. Select an existing File connection manager, or click <New connection...> to create a new connection manager.
Related topics: File Connection Manager Editor
Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
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. Use this parameter to specify filter criteria for local files. The criteria is specified using a FLEE expression. You can use the following elements from the System.IO FileInfo class: CreationTime, Directory, DirectoryName, Extension, FullName, IsReadOnly, LastAccessTime, LastWriteTime, Length, and Name. The following additional .NET Framework elements can also be used in FLEE expressions for filtering: DateTime, Now and Regex.
An example of an expression that can be used to get all zip files from the past 5 days is as follows: LastWriteTime> Now.AddDays(-5) and Name.EndsWith(".zip")
Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Select an existing connection manager, or click <New connection...> to create a new connection manager.
Related Topics: FTPS Connection Manager, REST Connection Manager, SharePoint Connection Manager, SSH Connection Manager, Salesforce Connection Manager
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. Select remote path on the remote server.
Select an existing user-defined variable, or click <New variable...> to create a new variable. 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
Specify whether or not a remote file can be overwritten.
If you are sending 0-length files, make sure this option is set to True. Otherwise the remote file will not be created.Specify whether or not the local file can be overwritten.
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. Select an existing File connection manager, or click <New connection...> to create a new connection manager.
Related topics: File Connection Manager
Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
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. Select an existing File connection manager, or click <New connection...> to create a new connection manager.
Related topics: File Connection Manager
Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
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
Indicate whether or not ResultVariable contains a string of remote file names or array of 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 string of remote file names separated with a newline (\n) character. 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):
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. Not visible in the task user interface.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. Not visible in the task user interface.Specify if remote or local files are recursively included. This parameter has the options listed in the following table.
Options Description True The task includes all remote or local files recursively, starting from the specified remote or local path. Only folders are allowed to be specified. False The task includes only the specified remote or local path files. This is the default option. 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. Use this parameter to specify filter criteria for remote files. The criteria is specified using a FLEE expression. You can use the following elements from the IFileInfo interface: Name, FullName, Size, ModifiedTime, and IsDirectory. The following additional .NET Framework elements can also be used in FLEE expressions for filtering: DateTime, Now and Regex.
An example of an expression that can be used to get all zip files from the past 5 days is as follows: Modifiedtime > Now.AddDays(-5) and Name.EndsWith(".zip")
Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Specify remote path separator. The default separator is a forward slash (/).
Specify additional options for the action. The parameter should be specified as name to value text, where each option is separated with newline like:
name1=value1
name2=value2
...
Retry settings
Use the Retry settings to configure if an automatic retry will be made when error occurs.
Optional. Specify to retry only if the error message matches the RegEx pattern.
Specify the number of seconds to pause between retries. The default is 30 seconds.
Specify the maximum number of retry attempts.
Knowledge Base
- Error Message: When performing a write operation with AllowWriteStreamBuffering set to false, you must either set ContentLength to a non-negative number or set SendChunked to true.
- Requirement for successful data streaming
- How can I move files on the remote server?
- Error Message: Local path is not a file path or you do not have permission to access it.
- Where can I find the documentation for the File Transfer Task?
Related scripts
Samples
What's New
- Fixed: Failure to set remote file date/time when uploading single file (Thank you, Jeff).
- Fixed: Specifying only wildcard for the Remote parameter (no folder) caused failure when using 'Receive Action' (Thank you, Mark).
- New: Support for Salesforce connection.
- New: A new AdditionalOptions parameter for specifying additional action options.
- New: Support for recursive upload of sub-folders and files.
- New: Parameters for Retry On Error.
- New: The result returned from 'Get remote files list' action can now be iterated using the standard 'Foreach ADO Enumerator'.
- Fixed: Local file modified time was incorrectly set to the remote file modified time when the local file exists and OverwriteLocal parameter was set to False.
- Breaking Change: Newline delimited result returned by 'Get remote files list' action now contains full-path names when ExtendedRemoteFileInfo is False.
- Introduced task.
Related documentation
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.