Dave Noderer's Blog

December 2009 Entries

CozyRoc SSIS Components

Recently I had a need to create a SQL Server 2008 SSIS package that could do SFTP transfers. The FTP component that comes with SSIS is very useful but it cannot do secure transfers.

In the past I have had to use scripting to create dynamic script files for WS_FTP pro (which I do use and like very much!!) but this brought in many more moving parts and was subject to failure for a number of reasons.

After a bit of searching I found http://www.cozyroc.com and in particular the SFTP component: http://www.cozyroc.com/ssis/sftp-task.

Due to the fair amount of complexity in the file names on both sides I was driving it from a script task.

The programming is slightly different from the normal ftp task and actually a bit simpler. The normal examples for using the ftp task did not work with cozyroc and after a fair amount of experimentation and searching I finally submitted a question to cozyrock.

Ivan Peev (Mr CozyRoc himself) responded with a sample script: http://www.cozyroc.com/script/ftp-file-exists-task that quickly showed me how to accomplish what I wanted!

The component is happily transferring files securely in the automated process and I know I will be referring CozyRoc to customers when this need arises.

This is only one of the many components in the CozyRoc package. There are also control flow tasks for zip, ssh, amazon s3 and stream objects and data flow for oracle, informix, db2, edi and sales force to name a few.