How To - Tasks and Transformations: FTP Task

It’s been a little while since my previous post as we’ve been heads down on our next Mist and Hadron releases. However, I’m back today to begin discussing the several Data Preparation SSIS tasks. I’ve already authored a post on the SSIS File System task and I’m continuing today with a discussion of the FTP task.

Background:

The SSIS FTP task allows you to perform various FTP operations in your package. The task implements eight different operations, although three are duplicated by the File System task:

Operation Description Duplicated by File System task Send files Sends a file from the local computer to the specified FTP server Receive files Downloads a file from a FTP server to the local computer Create local directory Creates a folder on the local computer X Create remote directory Creates a folder on a FTP server Remove local directory Deletes a folder on the local computer X Remove remote directory Deletes a folder on a FTP server Delete local files Deletes a file on the local computer X Delete remote files Deletes a file on a FTP server The FTP task can be useful in any workflow that needs to apply transformations to data, before or after moving the data to a different location. In this post, I’ll walkthrough using the SSIS FTP task in two examples. I’ll then demonstrate the same examples in Mist.

Using BIDS:

To get started with the FTP task, first find it in the Toolbox tool window.

Next, drag and drop the task onto the package’s design surface.

The red X icon indicates the task has errors. The Error List displays the following:

From the error list, it’s clear the task requires a connection. This makes sense considering the task needs to know how to connect to the FTP server.

To solve that problem, right click in the Connection Managers area and select New Connection… in the context menu.

This opens the Add SSIS Connection Manager dialog. Select the FTP connection from the list and press Add… to open another dialog for the FTP Connection Manager editor.

This is where you enter your FTP server settings, including the server name and port. Regarding credentials, the FTP connection manager supports anonymous authentication and basic authentication, but not Windows authentication.

Once you’ve filled in the dialog, be sure to test your connection to confirm it works. Then press OK to dismiss the dialog.

Next, double click on the FTP task to open its editor dialog.

In the General tab, there’s a conspicuous blank to the right of the FtpConnection property. Selecting the FtpConnection property displays a dropdown button. Click on the dropdown button to select the just created FTP Connection Manager.

Beneath the FtpConnection property is the StopOnFailure property. Its value indicates if the FTP task stops if an FTP operation fails.

Switching to the File Transfer group, the default operation for the task is Send Files. The send files operation transmits files from your local disk to the specified FTP server.

The Local Parameters group provides two properties for controlling the local file. The LocalPath property contains a string of the file name being sent. The path string can be entered manually, or obtained by creating a File Connection manager in the property’s dropdown list.

The IsLocalPathVariable indicates whether the LocalPath is set in a variable, as opposed to a string. If it’s set to true, the LocalPath property name changes to LocalVariable and its value is set by a variable list dropdown.

In the Remote Parameters group, there are three properties for controlling how the file is received on the FTP server. IsRemotePathVariable functions just like IsLocalPathVariable. If it’s set to false, the RemotePath property holds a string whose value is a path on the FTP server where the files are to be sent. If it’s true, then RemotePath points to a variable that holds the value. The OverwriteFileAtDest property indicates whether a file with the same name as a sent file can be overwritten in the RemotePath directory.

Along with the Operation property, the Operation group contains the IsTransferAscii property. This property controls whether a FTP transfer should occur in ASCII mode.

For the other FTP operations, you’ll see that Local Parameters properties appear for local operations (e.g. create local directory) and Remote Parameters properties appear for remote operations (e.g. delete remote files). However, their meanings remain the same.

A simple, and generally low risk, test for the FTP task is to create a remote directory at the root of your FTP server. To do this, select the Operation property, causing a dropdown button to appear. In the dropdown list, select Create remote directory.

This changes the parameters in the dialog box as follows:

Next, select the Remote Path property and click on the ellipses button.

This opens a directory browser dialog, allowing you to navigate to the desired directory, with the dialog generating the appropriate directory path.

After pressing OK, the selected location is stored in the RemotePath variable.

To create a directory beneath documentation, type /testDir after the end of the path.

Finally, press OK in the FTP Task Editor dialog to store the changes made to the FTP task.

Notice that now, the Error List is empty. If you press F5 to run the task, the directory will be added on your FTP server.

Now that the testDir directory is present, let’s send a file to it. First, open the FTP task again and set Stop on Failure to false.

If the directory already exists, we’ll continue to the next task.

Second, add an additional FTP task and link the tasks with a precedence constraint.

Third, double click on the Precedence Constraint to open the Precedence Constraint editor. Inside, switch the Evaluation Value from Success to Completion. Then press OK to save the changes.

Fourth, double click on FTP Task 1 to open its editor dialog. In the General group, set the FtpConnection to the previously created FTP Connection Manager.

Then switch to the File Transfer group. For the RemotePath property, use the same directory path that was created in the first FTP task. The notion is that the initial task creates the directory, if necessary, so the second task can copy files to it. Notice that I also set OverwriteFileAtDest to true, so newer versions of the file overwrite older ones.

In the Local Parameters group, select the LocalPath property, open its dropdown, and select New Connection…

This opens the File Connection Manager dialog, where you can browse to find the file to be sent, or type its path directly in the File textbox.

Once entered, press OK to dismiss the dialog.

Once complete, the dialog properties are set as follows:

Press OK to store your changes and then press F5 to run your package. This time, the directory will be created if it’s missing and then, the file will be sent to the directory on your FTP server.

If you’re wondering about sending multiple files, it can be done. In fact, you would use the same approach as when using the File System task to copy multiple files; use the For Each File enumerator to enumerate across files in a directory. See the Foreach File loop post for a demonstration.

Receiving multiple files from your FTP server doesn’t require a container task since the FTP task will send all files in the specified remote path to your specified local path.

It’s also worth noting that the FTP task supports the * and ? wildcard characters in paths. However, they can only be used in filenames. For instance, C:documentation*.txt won’t work but C:documentation.txt will.

Using Mist:

Now, let’s create the same FTP tasks in Mist.

To start, you’ll need to create a package. If you’re unfamiliar with how to create a package in Mist, you can follow the first two steps in the Mist User Guide’s Creating a Package topic.

Once your package is open, navigate to the Toolbox tool window.

Select and then drag the File System task onto the Package’s design surface.

The task has a red X icon, just like in BIDS, since it has errors.

Look below the package designer and select the Package Details tab to bring the Package Details tool window to the front. Within the Package Details tool window, you can edit the FTP task’s properties. The non-modal nature of the tool window allows you to make other changes in your package or project without having to cancel out of dialogs first.

In the BIDS sample, I started by creating a FTP task that creates a remote directory. To do that in Mist, open the Operation dropdown and select Create Remote Directory.

Of course, this task requires a connection to the FTP server. If you click on the FTP Connection dropdown in the Package Details pane, you’ll see that it only lists (No Selection). Thus, you’ll need to create a FTP connection. You can follow the steps in the Mist User Guide’s Creating New Connection section to create a connection, although you’ll want to make a FTP connection instead of an Ole Db connection.

The designer for the FTP connection has one red bordered field, clearly indicating that you need to provide a server name.

Matching the FTP Connection Manager editor dialog from the BIDS sample, provide an appropriate server name, along with any required authentication values.

Now, reopen the package and select the FTP task to view its details. In the FTP Connection combo box, you can now select the FTP connection.

The next step is to enter the remote directory path inside the Remote Path text box. The Method dropdown is set to Direct to indicate that the remote path value is being entered as text, as opposed to being provided via a variable.

To match the BIDS sample, also uncheck the Stop on Operation Failure checkbox.

Next, you need to create the second FTP task, which will send a file to the FTP server. To begin, drag and drop another FTP task onto the package design surface.

Then, drag a line from FTP Task 1’s bottom node to FTP Task 2’s top node. This will create a precedence constraint.

Notice that when the precedence constraint is created, a green line appears with an S. The letter S and the green color indicate that the precedence constraint’s evaluation value is success. To change that, click on the precedence constraint to select it. The package details area will change, displaying a data grid that lists the constraint, as you’re now seeing the precedence constraint’s details.

Double click on the Evaluation Value cell to display its combo box. Open the combo box and select Completion to change the constraint’s evaluation value.

Once done, the precedence constraint will become blue and its label’s letter will switch to C.

Then, click on FTP Task 2. This updates the Package Details window with the task’s settings. You can begin by selecting FtpConnection1 in task’s FTP Connection dropdown.

Also, enter the same remote path, as FTP Task 1, in the Remote Path text box.

Next, you will need to create a File connection, to specify the file being sent. You can follow the steps in the Mist User Guide for Creating a Connection but remember to make a File connection.

The designer for the File connection has one red bordered field, clearly indicating that you need to provide a file path.

Click on the ellipses button, to the right of the red border, to display an Open File dialog to help you find your file.

Matching the BIDS sample, provide an appropriate file path for your file connection. Also, feel free to rename the connection so it’s easy to identify.

Now, reopen the package and select FTP Task 2 and view its details again. In the Local Path’s File Connection combo box, you can now select FtpFileConnection.

With that, you’re now ready to build your project and open the generated assets in BIDS to run the package.

Next Time:

I’ll be continuing with the theme of Data Preparation tasks.

Links:

FTP documentation for the BIML language

FTP task - MSDN

-Craig