Within SSIS a FTP task exists which enables you to access a FTP server. However, it does not support Secure FTP (SFTP). There are many Secure FTP tools to choose from and each requires a unique set of commands and designs to be used within SSIS.

For this article, I am using Putty.org’s file transfer tool commonly known as PSFTP (a free open source tool). This tool performed as needed and we were able to build additional processes around it to enhance its use and satisfy our customers’ requirements.

Developing ETL code and attempting to achieve the ultimate business solution can have its challenges. There is always “a need for speed” or real-time business analysis. Using database analytical functions offered by leading database providers (e.g. Oracle, DB2, Microsoft SQL Server 2005 and Teradata) a developer can cut down on the amount of processing within the ETL tool and allow the database to assist with the processing.