File Transfer Activity in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

Task for file activities through FTP (File Transfer Protocol) has been introduced in DTS. With major improvement in SSIS, the task has even more functionality than ever before.

What is difference functionality between the task in DTS and SSIS? In DTS, you can only receive file through FTP, but in SSIS, not only you can receive, but also you can send, delete local or remote file. Here is complete function available in FTP task on SSIS : send/receive file, create/remove local/remote directory, delete local/remote files.

The task need connection to FTP type, so you need to create FTP type connection manager. In FTP Connection Manager Editor window, you specify FTP server name, by default FTP port is 21, but if your FTP is secure FTP, the port is usually 22. If your FTP requires any connection get authenticated before doing any file operation, specify it in UserName and Password in Credentials section. If you FTP uses passive mode, check it on Options section.

After you define FTP connection manager, you can create FTP task in Control Flow Designer. In FTP Task Editor window, select the FTP-based connection manager on FTPConnection property.

On File Transfer section, you will see similar properties with those of File System Tasks. For Local Parameters property group in FTP is similar to Source property group in File System task. And For Remote Parameters property group in FTP is similar to Destination property group in File System task. One unique property that attracts my attention is IsTransferAscii property, by default the value is false. Most of FTP clients the default is true, if your FTP file operates in Ascii transfer mode, adjust it to avoid content inconsistency when your files get transferred between local and remote connection.

With SSIS, Your package has much of FTP functionalities in file/directory operations ever than before.