Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 2012 SSIS – Sending files using FTP Task

By Raunak Jhawar,

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# in VB.NET or 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

SSIS FTP Task

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

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.

 FTP Connection Manager in SSIS

 Configure FTP Task to send multiple files

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

Package in SSIS FTP Task

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

Thanks | Raunak Jhawar | t:@raunakjhawar

Total article views: 6215 | Views in the last 30 days: 16
 
Related Articles
FORUM

DTS Designer Components Problems

Enterprise Edition couldn't be opened after uninstalling DTS Designer Components

BLOG

SQL Server 2005 Components

Ok! There is the SQL Server 2005 Components. There is the brief information about the components: D...

FORUM

Cannot connect to a remote server

Remote server connection issues

FORUM

Error installing SQL Server Workstation Components...

Error installing SQL Server Workstation Components...

FORUM

Query on SQL Server 2005 components

Query on SQL Server 2005 components

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones