SQL Server Integration Services FTP Task for Data Exchange

By:   |   Comments (21)   |   Related: More > Integration Services Control Flow Transformations


Problem

An FTP (File Transfer Protocol) server is often used for data exchanges in many data integration scenarios. SSIS includes an FTP task to download and upload data files to and from an FTP location, but how does this work and how can we configure it for data file downloads and uploads?

Solution

When data integration system or ETL processes can't connect directly to the data source system, an FTP server can be used as a temporary storage location for data exchange. Source systems can upload data to an FTP server and then the data integration system can download the data from the FTP server and import the data into the database.

SSIS being one of the leading ETL tools in the market includes an FTP task for working with an FTP server.  Downloading data files from an FTP server to the local machine and other tasks can be used to import the data from the locally downloaded data files to the database/data warehouse. Not only does the FTP Task allow downloading of the data files, but there are a host of other operations that can be performed with this task as mentioned below:

 

Operation

Description
Send files Used for uploading files from the local machine to the FTP server 
Receive files  Used for downloading files from the FTP server to the local machine 
Create local directory  Used to create a folder on the local machine 
Create remote directory  Used to creates a folder on the FTP server 
Remove local directory  Used to delete a folder on the local machine 
Remove remote directory  Used to delete a folder on the FTP server 
Delete local files  Used to delete a file on the local machine 
Delete remote files  Used to delete a file on the FTP server 

Connecting to an FTP Server Using Windows Explorer

There are different ways to connect to an FTP Server for data verification and one of the easiest ways is to use Windows Explorer.  Just specify the FTP Server name with FTP protocol as shown below and hit <Enter>.

There are different ways to connect to FTP Server for data verification

This will bring up a screen as shown below. Here we need to specify the credentials to connect to the FTP Server or choose to log on anonymously if the FTP Server allows anonymous access.

specify the FTP Server name with FTP protocol

Next click on the Log On button to log onto the specified FTP Server with the specified credentials. Based on permissions, it will display folders and files similarly to what's shown below:

click on Log On button

Using the FTP Task in an SSIS Package

In order to connect to an FTP Server, we first need to create an FTP connection manager which encapsulates information needed to connect to the FTP Server and the FTP task that uses that information at run time to connect to the server. In order to create a FTP connection manager create a new package and right click on the Connection Managers pane on the bottom and then select FTP as the connection manager type from the dialog box as shown below:

right click on the Connection Managers pane on the bottom and then select FTP as connection manager type f

Clicking on the Add button on the above dialog box will bring up an FTP Connection Manager Editor to specify the different information which will be used to connect to the FTP Server. For example, the FTP Server name, port, credentials to connect to the FTP Server, etc...

Clicking on Add button on the above dialog box will bring up a FTP Connection Manager Editor

Now you can drag an FTP Task from the SSIS Toolbox to the control flow, which will look similar to this:

drag the FTP task from the SSIS Toolbox to the control panel

Double click on the FTP task to change its properties in the Editor. The editor has 3 pages.  On the General page we need to select the FTP connection manager that we created above for connecting to the FTP Server and then specify an appropriate name and description for the component as shown below:

Double click on the FTP task to change its properties in the Editor

On the FTP Transfer page, we first need to select the operation that we want to perform (more about these different operations can be found in the beginning of this tip) with this FTP task based on the Local Parameters and Remote Parameters options will appear or disappear. For example, when we select "Receive files" as the operation type then both Local Parameters and Remote Parameters options will appear, but if I select the "Create remote directory" option then only the Remote Parameters option will appear.

the FTP Transfer page of FTP Task Editor

For this example I want to download a specific file from the FTP Server so I will chose "Receive files" as the operation type and then specify the remote parameters like location and name of the file to be downloaded and the local parameters like local machine folder where the downloaded file will be saved. Here, I can either specify hard-coded values for these parameters or the values can come from SSIS package variables making this process more dynamic.

imgF

As you might have noticed, we can select only one operation type for each FTP task and if we want to perform multiple operations we need to use multiple FTP tasks, one for each operation. Now if I execute the package we can see the file from the FTP Server getting downloaded and written to the folder that we specified. After the file has been downloaded I can then use a data flow task to load data from the locally downloaded file to the database.

the values can come from SSIS package variables

Downloading Multiple Files using a Wild Card Character

In the above example, I simply downloaded one file from the FTP Server to the local machine. But what if I want to download or upload all the text files or all the XML files in one go. Well in that scenario, we can use a wild card character. For example, as you can see below I want to download all text files from the specified FTP Server location to the local machine and hence I specified "*.txt" for the file name.

Downloading multiple files with wild card character

Now the above specified wild card character will download all the text files to the local machine from the FTP Server. To load each of these files to the database I can use a ForEach Loop container in SSIS as shown below.

the above specified wild card character will download all the text files to the local machine from the FTP Server

We can use the ForEach Loop Container with a ForEach File Enumerator and process all the data files from the locally downloaded folder; in other words, process each data file one at a time in a loop.

use ForEach Loop Container with ForEach File Enumerator and process all the data files

The FTP Task in SSIS does not support SFTP (Secured FTP), but there is a task available at codeplex which can be used for this scenario. 

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, December 8, 2017 - 12:19:36 AM - Ranjith Back To Top (73749)

 Is it possible to read a file directly from the FTP folder without storing them in the local folder and do the ETL operations in SISS?

My stackoverflow question is https://stackoverflow.com/questions/47708076/read-ftp-files-without-local-folder-in-ssis-package 

 


Wednesday, May 3, 2017 - 10:28:49 AM - Scott Back To Top (55413)

 To everyone wondering how to get a package to remember the FTP password: learn how to do package configuration (store password in an XML file, SQL table, commandline args, environment variables, etc)  A full discussion is beyond the scope of this reply.

SFTP: There are a number of third-party tools for this.  I use Pragmatic Works Task Factory, and a large part of that decision was their licensing model.  Any number of developers can freely use the tools in BIDS, you only have to license the servers you will deploy the packages to.  There are other tools available that can do the job, most of them will let you use them on a trial basis.

When I have to do multiple FTP actions in one package, I usually end up using a script task.  Create and configure the FTP connection manager, then in the script task use AcquireConnection to get an FtpClientConnection object.  You may find the available methods allow you to be much more creative in your solution than trying to string together and configure a bunch of separate tasks.

One issue I had recently was an FTP package that had worked for 10 years started blowing up when the other party's ISP started bouncing packets around Atlanta for an extra 10 hops and connection times went from 1 second to 45 seconds.  It didn't matter what number I used with the standard FTP connection manager for the Timeout property, the package failed at 30 seconds every time.  If anyone has a solution for this I would love to hear it.  My solution was to modify my script tasks to use FtpWebRequest objects instead of FtpClentConnection, which didn't reduce the connection time but did respect a 60-sec connection timeout value.


Thursday, September 22, 2016 - 3:38:00 AM - Mutiara Back To Top (43386)

 Dear Arshad... 

 

Please help me.. I'm using SSIS 2012 here, and I need to download a file from AS/400 using SSIS FTP task, it's a fixed text file. I've already define the remote path with something like this, /QSYS.LIB/u38000UXX.lib/detail1.FILE , but it's always response with this 

[FTP Task] Error: File represented by "/QSYS.LIB/u38000UXX.lib/detail1.FILE" does not exist.

But if i try to download the same file using ftp trough command prompt, it's downloaded successfully

Any idea what I should check or change here?

Thanks

 

 


Wednesday, January 13, 2016 - 1:25:39 PM - Syed Faiz Back To Top (40413)

 Hi Arshad,

Thanks for sharing this article. Do you have any article which deals with SFTP connection instead of FTP connection?

 


Monday, October 5, 2015 - 7:58:57 AM - Vopros Back To Top (38824)

I have issue. When I done conteiner I received result: 6000 records from only 1 file. But I had above 60 files and 100 records in each. I recived all records from 1 file dublicated as many times as I have files. Pease help

 


Thursday, October 1, 2015 - 2:04:19 PM - Mahfooz Elahi Back To Top (38805)

 

I am able to do the FTP through MS Visual Studio but when I try to schedule it as part of a .dtsx package it gives me an error saying "Password was not allowed". I understand that the XML file does not store the password. I am not sure how to solve this issue. Any help will be appreciated.


Tuesday, February 3, 2015 - 1:21:59 AM - Sambhav Back To Top (36138)

 

Please give me solution for set encrption type in FTP Task in ssis.


Wednesday, January 21, 2015 - 8:26:29 AM - Dheeraj Back To Top (36007)

Arshad, this is very helpful in automating one process that I have been spending a lot of time on. Can you please help me with the next steps, i.e., the Data Flow task? Now that I have 5 CSV files in my local system, how do I go about loading them to SQL tables.

Would really appreciate any guidance on this.


Monday, October 6, 2014 - 2:35:15 PM - Arif Hassan Back To Top (34848)

How do you set up ssis ftp package into ssms job, I am getting error message, only way I can do is to use encrypted password. Is there any work around?


Tuesday, September 30, 2014 - 3:36:39 PM - Golam Kabir Back To Top (34781)

This is a great tip for me - thanks Arshad.


Thursday, September 18, 2014 - 11:13:39 AM - MGaylard Back To Top (34604)

Concise, clear and accurate instructions

 

Thank you


Tuesday, August 26, 2014 - 12:57:01 PM - Liz Back To Top (34272)

I can't seem to get the FTP connection manager to save the password. How do I get the ftp password saved?


Tuesday, April 15, 2014 - 7:19:27 PM - Jay Patel Back To Top (30075)

I am trying to ftp file(s) from and to AS400 using ssis 2005 sql server. The ssis runs successfully but files are not sending or receiving. Any suggestions.


Tuesday, March 18, 2014 - 1:51:06 PM - Matt Massey Back To Top (29802)

Great write up! This really helped me out as today was my first time setting up an FTP Task. Thanks!


Thursday, February 13, 2014 - 2:21:38 PM - Ravi Ramaswamy Back To Top (29434)

Arshad,

Nice one. Easy and simple

Thanks,
Ravi Ramaswamy
 


Thursday, July 25, 2013 - 5:17:18 AM - SQLDBA Back To Top (25994)

I am using SSIS package.

FTP Task Editor=>

IsLocalPathVarible:- True
Local Varible :- User::Final

Operation :Send Files
ISTransferASCII :- False

Remote Parameter
IsRemotePathVarible :-False

REmote Path :- \Location
OverwriteFileAtDest :- YES

Final:\test20130724_230456_662000000.xls which having expression
"D:\\test"+Replace(Replace(Replace(Replace((DT_WSTR,50)(getdate()),"-","")," ","_"),":",""),".","_")+".xls"

But [Connection manager "FTP Connection Manager"] Error: An error occurred in the requested FTP operation. Detailed error description: The filename, directory name, or volume label syntax is incorrect. .
e SSMS formatting.


Friday, June 7, 2013 - 1:44:08 PM - hjump51 Back To Top (25343)

Steps to connect to SFTP within SSIS using BIDS.

 

1) link to setup SFTP task in SSIS  

http://ssissftp.codeplex.com/documentation

 

2) drag and drop for Windows 7 of SFTP dll

http://geekswithblogs.net/NibblesAndBits/archive/2012/04/09/adding-a-dll-to-the-gac-in-windows-7.aspx

 
 

 


Wednesday, June 5, 2013 - 12:44:04 PM - Mario Back To Top (25312)

Hi,

Greate article, I was wonder if is possible to connect to a SFTP Server using FTP Task?

Thanks


Sunday, May 12, 2013 - 10:07:03 AM - Arshad Ali Back To Top (23905)

Hi Yang, good to know it was helpful to you, thanks for your feedback.

 

Hi Chandu, yes you can use wildcard characters to download or upload files with specific naming pattern.


Saturday, May 11, 2013 - 10:59:23 AM - Yang Back To Top (23901)

Dear Arshad,

It is really helpful!

Even yesterday I was struggling with the FTP file downloading and loading into database. Now it is all solved with your  article!

Thanks!

Yang


Wednesday, May 1, 2013 - 6:51:20 PM - chandu Back To Top (23673)

Arshad,

 

Is it possible to place a dynamic criteria  for wildcharecters  for receiving files from a  ftp folder ,like a  specific date based expression criteria such as last  day of evefry month file from a list of files with date stamp.

 

 

Many Thanks

Chandu















get free sql tips
agree to terms