SQL Server 2012 SSIS: Sending files using FTP Task

SQL Server 2012 Denali CTP3 SSIS – Sending files using FTP Task

Hello!

We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”.

In the last post, we had a short walkthrough for receiving files from an SFTP server using SSIS Execute Process Task. You can find the published content here

In this post, we shall send files or upload files to a remote server using FTP.

We have two distinct approaches available:

• Develop a script task either in C# or in VB.NET which will perform a actions like create a remote session, perform the required action on the server and lastly gracefully exit the session
• Use a FTP Task and configure the SSIS component that suits our need

We shall use the FTP Task in the post to send files to the remote server.

Drag the FTP Task component on the designer window

Now double click on the component. This will open the FTP Task Editor.

Using this window, one can establish a new FTP connection manager; configure the task for a specific FTP operation (such as Send Files, Receive Files etc.) and also add any expression to tweak the task execution.

Note:

• You may configure the FTP is passive mode by checking the option – Use Passive Mode
• Chunk Size is the size of each packet involved in the transfer. This has a default of 1KB and can have a maximum size of 1MB in SSIS. This is the upper limit. One will have to resort to designing a custom component if a larger chunk size is desired.
• Using this design, only one file can be sent to the remote destination. If this is not the requirement, then the package designer will have to introduce a looping mechanism, in order to loop over a set of files
• Also, data transfer can take place either in ASCII or in Binary format. ASCII can be used for plain text files and files which have strings numbers, on the hand if we have files such as {.zip, .exe, image files or rich text files} Binary transfer mode is to be used
• If ASCII mode is used for a binary file, the file will end up getting corrupted

Let us take up a small project to send multiple files to a remote server using FTP

Here since we have to send multiple files, I have used a FEL (for each loop) task to loop on the directory content. The FEL and FTP task will do the following actions:

• Loop on the directory
• Pick one file and send file using FTP
• Repeat the action till all files(*.*) in the directory are FTP’ed 

That’s all. Happy learning

In the next post, we shall see yet another exciting feature in SSIS which is called a parameterize the task

Find us on Facebook at http://www.FaceBook.com/SQLServerGeeks and Twitter at @SqlServerGeeks

Thanks | Raunak Jhawar | t:@raunakjhawar