The SSIS FTP Task is used to perform different kind of operations on Files and Folders (or Directories). For instance, if you want to send or receive the files from FTP Server to local directory we can use this SSIS FTP Task.
NOTE: The SSIS FTP Task uses the FTP Connection Manger to connect with the Remote (FTP Server) Files and Folders.
In this article we will show you the steps involved in configuring the FTP task in SQL Server Integration Services with example. Before we start configuring the SSIS FTP Task, Let us see the files and folders available in our FTP Server. Below screenshot will show the same.
Configure SSIS FTP Task
First, Drag and drop the FTP Task into the Control Flow region and rename it as SSIS FTP Task
Double click on it will open the SSIS FTP Task Editor to configure it.
- Name: Please provide the Unique Name
- Description: Briefly describe the Task Functionality. It is always a good practice to provide the valid description.
Within the Connection section, We have one properties called FTPConnection:
- FTPConnection: By clicking on the drop down arrow will show you the already created FTP Connections (If any) or please click on the <New Connection..> option to create one. Let us see what happen when click on the <New Connection..> option
Once you click on the <New Connection..> option, FTP Connection Manager will be opened to configure the connection settings. Please refer SSIS FTP Connection Manager article to understand the connection settings
StopOnFailure: This property hold two options: TRUE and FALSE.
- If you set this property to TRUE then, FTP task will fail when there is an issue in FTP operation
- When you set this property to FALSE then, SSIS FTP task will not fail even there is an issue in FTP operation
Please click on the File Transfer tab to configure the FTP operations. Following screenshot will show you the available properties in this tab. Within the Operation section we have IsTransferAscii option.
- IsTransferAscii: This property has two options: True and False. If we set this property to true then FTP task use ASCII mode for its operations.
The FTP task in SSIS supports eight different operation on Files and Folders. Please see the available options in below screenshot
|Send Files||This will send files from Local Computer to FTP Server. Please refer SSIS FTP Task Send Files article to send single file and SSIS FTP Task Send Multiple Files article to send multiple files.|
|Receive Files||This will import files from remote directory to local computer. Please refer SSIS FTP Task Receive Files article to receive single file and SSIS FTP Task Receive Multiple Files article to receive multiple files.|
|Create Local Directory||Using this options, We can create the directory (or folder) in the local computer. Please refer SSIS FTP Task Create Local Directory article to Create Local Directory.|
|Create Remote Directory||Using this options, We can create the directory (or folder) in the FTP server. Please refer SSIS FTP Task Create Remote Directory article to Create remote Directory.|
|Remove Local Directory||Using this options, We can remove the directory (or folder) in local computer. Please refer SSIS FTP Task Delete Local Directory article to delete Local Directory.|
|Remove Remote Directory||Use this option to delete the directory (or folder) in the local computer. Please refer SSIS FTP Task Delete Remote Directory article to delete remote Directory.|
|Delete Local Files||This will delete files on the local computer. Please refer SSIS FTP Task Delete Local Files article to delete files from our local file system.|
|Delete Remote Files||This will delete files on the FTP Server. Please refer SSIS FTP Task Delete Remote Files article to delete files from FTP Server.|
SSIS FTP Task Local Parameters
IsLocalPathVariable: This property has two options: True and False. If we set this property to true then local path is stored in a variable. If we set this property to false then we have to select the local path manually using File Connection Manger.
When we set the IsLocalPathVariable to true then new property called LocalVariable will be appeared.
LocalVariable: This property displays all the available user variables. Please select the appropriate variable which is holding the Local Path (File or Folder path). If you haven’t created any variable before then please click on the <New Variable..> as shown in the below screenshot.
Once you click on the <New Variable..>, it will open the new window called Add Variable to create the new variable. Here you can create the new variable which will hold the local source path.
When you set the IsLocalPathVariable to False then we have to configure the Source Connection using LocalPath Property. If you already created the File Connection Manager you can simply select it from the drop down list.
If you haven’t created any connection Manager before, You have to create by selecting <New Connection..>. as shown in below screenshot.
Once you selected the <New Connection..> for files and folders, File Connection Manager Editor will be opened in new window. Using this editor we have to configure the connection with files and folders.
Once you selected the required file, Click on the OK button
Configure SSIS FTP Task Remote Parameters
IsRemotePathVariable: This property has two options: True and False. If we set this property to true then we have to access the Remote path from the variable. If we set this property to false then we have to select the Remote path manually using FTP Connection Manger (which we created in our General Tab).
When we set the IsRemotePathVariable to False then we have to configure the remote Connection using RemotePath Property. Please click on the browse (…) button besides this option to open the remote directory. From the below screenshot you can observe the / as the location because it is the root directory of FTP server. Currently we don’t have any folders to select so we are selecting the root directory as the location to receive the file from local computer.
OverwriteDetination: This property has two options: True and False. If we set this property to true then SSIS FTP Task will overwrite the existing files in the Destination path.
Click OK to finish configuring SSIS FTP Task Editor.
Thank You for Visiting Our Blog.