Tutorial - SFTP Task for SSIS
By Spencer Brown - Technical Support, /n software.
Introduction
The /n software SQL Server SSIS Tasks consist of a single task library containing individual tasks for Internet communications. Built from the same core codebase as the /n software Adapters for Microsoft BizTalk Server, the SSIS Tasks provide robust enterprise ready Internet connectivity, secure messaging, and file transfer.
This guide will focus specifically on the SFTP task, which is used to communicate with SFTP servers. SFTP (which should not be
confused with FTP, FTPS, or FTP over SSH) is the SSH File Transfer Protocol. If you're interested in using SFTP communication in your SSIS package, you're in the right place. Before you continue reading, I recommend that you go ahead and download the product and follow along with me through the tutorial.
Contents
- Task Installation
- Send Task Properties
- Send Task Usage
- Receive Task Properties
- Receive Task Usage
- Using Expressions With The Tasks
- Conclusion
After you run the setup application, all of the necessary files will be installed on your system. However, in order
for the tasks to be available to you when designing your package you'll need to add them to the toolbox in Visual Studio.
Lets do this now by following these steps.
- Open Visual Studio and create a new Integration Services project.
- Expand the toolbox in Visual Studio and right click on the "Control Flow Items" tab then select "Choose Items".
- Since the tasks were installed to the GAC you will see the tasks listed under the "SSIS Control Flow Items" tab of the "Choose Toolbox Items" dialog.
- Check the box next to the tasks you wish to add. For this tutorial we will add "nsoftware SFTPReceive Task" and "nsoftware SFTPSend Task"
- After clicking OK you will see the tasks listed under the "Control Flow Items" tab in the toolbox
At this point let's go ahead and drag the nsoftware SFTPSend Task from the toolbox onto the Control Flow surface. You'll notice immediately that package validation is failing.
This is because some required properties are not yet set. Let us review the available properties, and in the next section we will discuss common settings. At this point you
can double click the task on the Control Flow surface to open the SFTP Task Designer. Under the SFTP Properties section you will see:
- Firewall Properties
The first group of properties are Firewall properties. These are only necessary if you have to pass through a local firewall on
the way to the server. In most cases, you won't need to worry about this, but in the event that you do, three firewall
types are supported here: SOCKS4, SOCKS5, and tunneling firewalls. Specify the type of firewall to pass through using the Firewall Type property. Then
set the Firewall Host and Firewall Port to the IP address and port to use on the firewall.
Finally, if your firewall requires authentication set the Firewall User and Firewall Password properties.
It is very important to understand these SSH properties, since a complete understanding will help ensure the security of your transactions. To that end, I will go into a more complete discussion of each of these properties.
The "SSH Cert" property will allow you to specify a certificate in a variety of forms as mentioned above. The design of the certificate selector dialog is intended to allow you to easily select a certificate for public key authentication. If you are specifying a PEM File, please note that you must specify a SSH2 RSA private key. Note: If you are selecting a PEM key without a public key certificate you will see a warning indicating that a private key was found, but no public key certificate was found along with the PEM data. This is ok and you can select Yes on this warning dialog to continue.
The "SSH Accept Server Host Key" setting tells the task what host key to accept during the security handshaking procedure of the connection
to the remote server. If the value specified here does not match the key that the server presents, the connection will be terminated with an
error. This property may be set to either the full, Base64-encoded text of the server key or its 16-byte MD5 fingerprint. If you choose to
specify the fingerprint, you may use either the Base-64 encoded fingerprint or you can HEX-encode it in the form "0a:1b:2c:3d". You may
also use the keywords "ANY" or "ALL" to instruct the task to accept any server host key. "ANY" or "ALL" should only be used for
testing purposes.
The "SSH Auth Mode" specifies which form of authentication you would like to perform when connecting to the SSH server. The task supports
either regular "User/Password" authentication, or public key authentication. If you choose to use public key authentication, you'll also
need to set the "SSH Cert" property to point the task to the key pair it should use. This article is not meant to explain how public key
authentication works, however if you would like to use this form of authentication you should generate a key pair on your own computer,
and copy the public key to the SSH server. Then when you connect to the server, you can point the task to your private key to authenticate. The "SSH Cert" property is only used if you choose to use public key authentication.
The "SSH Compression Algorithm" property should be set to "ZLIB" if you'd like to implement ZLIB compression in your SSH communications.
Generally, it's a good idea to compress large documents but inefficient to use it for small documents.
The "SSH Host", "SSH Port", "SSH User"
and "SSH Password" settings are all self explanatory - just specify the server you'd like
to connect to, on what port (the default is port 22), and the user and password to use to connect with. Note that if you use public key
authentication, you will still need to specify an "SSH User", but the "SSH Password" will be ignored.
In order to configure a send task to upload files from the LocalFile to my remote
SFTP server, I only need to set a few of these properties:
- I'll leave the firewall properties to their defaults since I don't need to pass
through any proxies to reach my server.
- As for the SFTP properties, I'll set the "Remote File" and the "Remote Path" to the name of the specific folder location
on the remote server where I want to store the files (in my case "\users\test\temp\").
- For the SSH properties, I'll start by using the value "ANY" for the "SSH
Accept Server Host Key", just for testing. Later I'll change this
to the correct value as documented above. I'll use public key authentication
since it is generally considered to be more secure,
so I'll set the "SSH Auth
Mode" to "Public Key". I'll get my certificate from a PEM file, so I'll use the certificate selector dialog from the "SSH Cert" to specify the PEM File. Then all that's left is to set the "SSH Host" and "SSH User" and I'm ready to go.
Another common situation in which the SFTP task might be used is one where files that are
dropped in a folder on a remote SFTP folder need to be retrieved so that these remote files can be used
locally or automatically
transferred elsewhere. In this situation, a receive task would be configured to connect to a remote server and retrieve the files.
After you add a new SFTPReceive task, you can double click the task like you did for the send task to
edit the properties. Many of these properties are the same as those documented
above in the Send Port section. Here I will only document the properties specific
to using the SFTPReceive task, since many of the properties have the
same meaning as in the above description of the SFTPSend task.
- Firewall Properties
These are all the same as in a SFTPSend task.
- SFTP Properties
The next group of properties are SFTP properties. This is where you will tell the
task where on the SFTP server to retrieve the files. Again, these properties
are fairly self explanatory:
|
Delete |
Whether or not the remote file should be deleted after download. |
|
DirList |
The collection is made up of entries for each listing in the current directory, specified by the RemotePath property.
The variable is in the form of a datatable. The table's rows represent a directory listing, and the columns represent
a different property of the listing. The columns of the datatable are listed below:
- (0) FileName - The name of the file being listed.
- (1) FileSize - The size of the file.
- (2) FileTime - The creation date of the file.
- (3) IsDir - Denotes whether the listing is a directory or a file.
- (4) Listing - The filename, and extended file information.
|
|
File Mask |
Indicates the type of files to download from the FTP server (for example, "*.txt"). |
Local File |
This may be set to either a specific file name, or a directory to download all files to. If you set this to a directory, the local filename will be the same as the original file name as it exists on the server. |
|
Operation |
This specifies the action of the task. Possible values are opListDirectory, opReceiveFiles (default), and opDeleteFiles.
|
|
Overwrite |
If set to false, an error will occur if a file with the same name already exists
on the file system. If set to true, the file of the same name will be overwritten. |
|
Received File Date |
This will contain the date of the last file processed. |
|
Received File Name |
This will contain the name of the last file processed. |
|
Received File Size |
This will contain the size of the last file processed. |
- SSH Properties
The task has the same SSH properties in a receive task as in a send task.
In order to configure a receive task to download files from the remote SFTP server,
again I only need to set a few of these properties:
- I'll leave the firewall properties to their defaults since I don't need to pass
through any proxies to reach my server.
- As for the SFTP properties, I'll set "File Mask"
to *.* (because I want to get all the files), and "Local File"
to "c:\temp" (all received files will be written to this location).
- For the SSH properties, I'll use the same settings as in the above send port example
since I am connecting to the same server.
-
I've also set the DirList property to a user variable of type object that I created. In my package I will
use a Foreach Loop (with a Foreach ADO Enumerator) to iterate over the data table and log the names of the files I've downloaded.
You may use the Task Designer to specify an expression. For instance:
Alternatively, you may point the property to a user variable by setting the property value as "User::MyVariable". This is especially helpful
for the password properties which are marked as sensitive and will not show up when creating an expression from the Task Designer. For information on how to
set a certificate by using expressions please see KB Entry 03070801.
Note that if you specify an expression for a required property you will need to set Delay Validation for the task to True.
This article demonstrates the ease of use of the SFTP task in particular, but
/n software provides a
full set of tasks for connecting to various types of Internet
servers.
The task properties are kept to a minimum for simplicity, but we make an effort
to provide those properties that are necessary for effective control over configurations.
We appreciate your feedback. If you have any questions, comments, or
suggestions about this article please contact our support team at
kb@nsoftware.com. For technical support please e-mail support@nsoftware.com.
