Tags: 
1.7, Task, File Transfer

Overview

The File Transfer Task is an SSIS Control Flow task for remote file transfer and management.
Note:If you have issues running the File Transfer Task under the SQL Server Job Agent, please check the FAQ here.


Demonstration


    Setup

    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.

    Parameters

    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.
    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, IncludeSubfolders, IsRemoteFilterVariable, RemoteFilter, RemoteFilterVariable.
    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, IncludeSubfolders, IsRemoteFilterVariable, RemoteFilter, RemoteFilterVariable.
    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, IncludeSubfolders, IsRemoteFilterVariable, RemoteFilter, RemoteFilterVariable.

    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.

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

    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

    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 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.

    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.

    Specify remote files filter criteria. The criteria is specified using FLEE expression. You can use the following elements from IFileInfo interface: 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")

    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 (/).


    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.

    Related documentation