SQL Server Integration Services SSIS FTP Task for Data Exchange By: Arshad Ali Ali | Transformations Transformati ons
enter email addres address
Join
Problem
Read Comments Comments (17)
|
Related Related Tips: More More > > Integration Services Control Flow
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 f or data f ile 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 t he leading ETL t ools in the market includes an FTP t ask for working with an FT P 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
Se nd f ile s
Us ed f or uploading f iles f rom t he loc al mac hine t o t he F TP se rve r
Receive files
Used for downloading files from the FTP server to the local machine
Create local direct ory
Used to create a folder on the local machine
Create remote direct ory
Used to creates a folder on the FTP server
Remove local direct ory
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 FT P Serve r 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 spec ify the F TP Server name with FT P protocol as shown below and hit .
Latest from MSSQLTips SQL Server Performance Considerations - Fetching Rows One By One or All At Once Power BI Desktop Custom Visualization Vote for your favorite MSSQLTips authors Why You Should Avoid SELECT * in SQL Server T-SQL Code On-Demand Webcast - Monitoring and Managing SQL Server Scheduled Tasks On-Demand Webcast - Unify IT Performance Monitoring and Optimization With Uptime Infrastructure Monitoring T-SQL Enhancements in SQL Server 2016 8 Things to do on LinkedIn When Starting Your Job Search
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.
permissions, it will display folders and files similarly to what's shown below:
Using the FT P T ask 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:
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...
Now you c an drag an FT P T ask from the SSIS Toolbox to t he c ontrol flow, which w ill look similar to this:
Double click on the FT P task to c hange 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:
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 "Rece ive files" as the operation t ype t hen 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.
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 t he downloaded file will be saved. Here, I can eit her specify hard-coded values for these parameters or the values can come from SSIS package variables making this process more dynamic.
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.
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.
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.
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.
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 Review Get List of Files from an FT P Server in SQL Server Integration Services Review SSIS SFTP Task Control Flow Component on codeplex Review other SSIS control flow component tips
Last Update: 2/15/2013
About the author Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.
Related Resources More Business Inte lligence T ips...
View all my tips
Print
Learn More
Share
1
Share
1
Tweet
+2
Become a paid author
Post a comment or let the author know thi s tip helped you. All comments are reviewed, so stay on subject or we may delete your comment. *Name
*Email
Notify for updates
Paragraph *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS a paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
Note: your email address is not published. Required fields are marked with an ast erisk (*)
Get free SQL tips:
*Enter Code
Save Comment
Monday, October 05, 2015 - 7:58:57 AM - Vopros
Read The Tip
I have issue. When I done cont einer I received result: 6000 records from only 1 f ile. 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 01, 2015 - 2:04:19 PM - Mahfooz Elahi
Read The Tip
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 t he password. I am not sure how to solve this issue. Any help will be appreciated.
Tuesday, February 03, 2015 - 1:21:59 AM - Sambhav
Read The Tip
Please give me solution for set encrption type in FTP Task in ssis.
Wednesday, January 21, 2015 - 8:26:29 AM - Dheeraj
Read The Tip
Arshad, this is very helpful in automating one process that I have been spending a lot of t ime on. Can y ou 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 06, 2014 - 2:35:15 PM - Arif Hassan
Read The Tip
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
Read The Tip
This is a great tip for me - thanks Arshad.
Thursday, September 18, 2014 - 11:13:39 AM - MGaylard
Read The Tip
Concise, clear and accurate instructions
Thank you
Tuesday, August 26, 2014 - 12:57:01 PM - Liz
Read The Tip
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
Read The Tip
I am trying to ftp file(s) from and to AS400 using ssis 2005 sql server. The ssis runs succe ssfully but files are not sending or receiving. Any suggestions.
Tuesday, March 18, 2014 - 1:51:06 PM - Matt Massey
Read The Tip
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
Read The Tip
Arshad, Nice one. Easy and simple Thanks, Ravi Ramaswamy
Thursday, July 25, 2013 - 5:17:18 AM - SQLDBA I am using SSIS package.
Read The Tip
FT P T ask Editor=> IsLocalPathVarible:- True Local Varible :- User::Final Operation :Send Files ISTransferASCII :- False Remote Parameter IsRemotePat hVarible :-F alse 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 Connec tion 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 07, 2013 - 1:44:08 PM - hjump51
Read The Tip
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 05, 2013 - 12:44:04 PM - Mario Hi,
Read The Tip
Thanks
Sunday, May 12, 2013 - 10:07:03 AM - Arshad Ali
Read The Tip
Hi Yang, good to know it was helpful to you, thanks for your feedback.
Hi Chandu, yes you can use wildcard c haracters t o download or upload files with spec ific naming pattern.
Saturday, May 11, 2013 - 10:59:23 AM - Yang
Read The Tip
Dear Arshad, It is really helpful! Even yest erday I was st ruggling with the FTP file downloading and loading into dat abase. Now it is all solved with your article! Thanks! Yang
Wednesday, May 01, 2013 - 6:51:20 PM - chandu
Read The Tip
Arshad,
Is it possible to place a dynamic criteria for wildcharect ers for receiving files from a ftp folder ,like a specific date based expression criteria such as last day of evef ry month file from a list of files with date st amp.
Many Thanks Chandu
24/7 SQL Server Monitoring Tools | SQL Diagnostic Manager | Try for Free How to find & fix SQL Server performance issues. 26 free monitoring tips for DBAs. Sign up now Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Free SQL Server T utorials - Stored Procedures, Performance Monitoring, Query Plans, SSIS and more Free Learning – Click here to become a better SQL Server Professional
Follow
Learning
Resources
Search
Community
More Info
Get Free SQL Tips
DBAs
Tutorials
Tip Categories
First Timer?
Join
Twitter
Developers
Webcasts
Search By T ipID
Pictures
About
LinkedIn
BI Professionals
Whitepapers
Authors
Free T-shirt
Copyright
Google+
Careers
Tools
Contribute
Privacy
Facebook
Q and A
Events
Disclaimer
Pinterest
Today's Tip
User Groups
Feedback
Author of t he Year
Advertise
RSS
Copyright (c ) 2006-2015 Edgewood Solutions, LLC All rights reserved Some na mes and products listed are the registered trademarks o f their respective owners.
