Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Get List of Files from an FTP Server in SQL Server Integration Services

MSSQLTips author Ray Barley By:   |   Read Comments (19)   |   Related Tips: More > Integration Services Control Flow Transformations

Problem
We use the FTP task in SSIS to process a number of files from an FTP server.  We would like to implement a step in our SSIS packages that would retrieve the list of files that are available on the FTP server before we try to process them.  The FTP task doesn't have an operation that will retrieve the list of files.  Can you provide an example of how to do this?

Solution
All of the code for this tip can be downloaded here.

The FTP task provides the following operations as shown below:

As you have noted, there is no operation to retrieve a list of files.  You can use the Script task to do this.  Here is a sample SSIS package that we will review to get the list of files from an FTP server:

The main points about the above SSIS package are:

  • Get Ftp File Listing is a Script task that will retrieve the list of files available on the FTP server.  It will return the list of available files as an XML document.
  • Stage Ftp File Listing is an Execute SQL task which will call a stored procedure to insert the list of files from the XML document into a table.

SSIS Package Setup

Before digging in to the package tasks shown above, let's discuss the package setup.  The following variables are defined in the SSIS package:

The variables are used as follows:

  • FtpFileListXML will be populated with the list of files available on the FTP server by the Get Ftp File Listing Script task
  • FtpFileType is used to specify the type of file
  • FtpServer is the URL of the Ftp server; e.g. ftp.yourservername.com
  • FtpWorkingDirectory is the directory or folder on the Ftp server to get the list of files from

The FtpFileType, FtpServer, and FtpWorkingDirectory variables will be set on the command line when we run the package.  This will allow our package to retrieve the list of files from any or our ftp servers.

The package uses the following Connection Managers:

  • OLE DB Connection called Staging which is the SQL Server database where the list of files from the FTP server will be written
  • FTP Connection which defines the FTP Server; the ServerName property is set to the FtpServer package variable

Get Ftp File Listing

Let's take a look at the Script task that is used to get the list of files from the FTP server.  The Script page of the editor is shown below:

The FtpWorkingDirectory variable allows the package to specify the folder on the FTP server to query for the list of files.  The script code will populate the FtpFileListXML variable with the list of files available on the FTP server as an XML document.

Now let's review the actual code in the Script task:

The main points about the above code are as follows:

  • Step 1: declare a StringBuilder variable to build up the XML document containing the list of available files on the FTP server
  • Step 2: setup the FtpClientConnection object using an FTP Connection Manager defined in the package; the FtpClientConnection class is provided in the Microsoft.SqlServer.Dts.Runtime namespace; the ftpFileNames array will be populated with the list of files; the ftpFolderNames array will be populated with the list of subfolders
  • Step 3: connect to the FTP server and get the list of files and subfolders from the folder specified by the FtpWorkingDirectory variable
  • Step 4: iterate through the ftpFileNames array and build an XML document with the list of file names
  • Step 5: set the FtpFileListXML package variable to the XML document containing the list of file names

Stage Ftp File Listing

Let's take a look at the Execute SQL task that inserts the list of files available on the FTP server into a table.  The create table script is as follows:

CREATE TABLE dbo.stg_FtpFileList
(
 FileType nvarchar(50) NOT NULL, 
 FileName nvarchar(50) NOT NULL
)

The following stored procedure is used to shred the XML document created in the Get Ftp File Listing task and insert the available files into the table above:

CREATE PROCEDURE [dbo].[usp_PutFtpFileList]
  @fileType nvarchar(50)
, @xml  xml
AS
BEGIN
  SET NOCOUNT ON;
 
  DELETE FROM [dbo].[stg_FtpFileList]
  WHERE [FileType] = @fileType
 
  INSERT INTO [dbo].[stg_FtpFileList] (
         [FileType]
        ,[FileName]
  )
  SELECT
   @fileType
  ,doc.col.value('@name', 'nvarchar(50)') filename
  FROM @xml.nodes('//file') doc(col)
END

The above stored procedure takes advantage of new XML capabilities provided in SQL Server 2005; take a look at our previous tip Replacing OPENXML with the XML nodes() Function in SQL Server 2005 for the details.

Running the Package

To run the package use DTEXEC and specify values for the FtpFileType, FtpServer, and FtpWorkingDirectory variables on the command line; e.g. here is a batch file that you could use:

SET FTPSERVER=ftp.yourserver.com
SET FTPFILETYPE=XXX
SET FTPWORKINGDIRECTORY=/yourftpfolder

DTEXEC /FILE GetFtpFileListing.dtsx [variable assignments go here]

The entire DTEXEC command must be specified on a single line; replace [variable assignments go here] with the following:

/SET \Package.Variables[FtpFileType].Properties[Value];%FTPFILETYPE%
/SET \Package.Variables[FtpServer].Properties[Value];%FTPSERVER%
/SET \Package.Variables[FtpWorkingDirectory].Properties[Value];%FTPWORKINGDIRECTORY%

After running the package you can query the stg_FtpFileList table to get the list of files available in a particular folder on an FTP server; e.g.:

Next Steps

  • While SSIS provides quite a few useful tasks, occasionally you need something more.  The Script task is likely a good alternative as it allows you to execute any VB .NET code inside of your SSIS package.
  • Take a look at the sample code here to experiment on your own.
  • The FtpClientConnection class used in the above example has other properties and methods that you may find useful; you can get the details here.
  • The FTP task also has other properties that you may need to set such as, user name, password, etc.


Last Update: 12/8/2008


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Tuesday, May 05, 2009 - 5:05:58 PM - Shirl Read The Tip

This is a great example for FTP data transfer! Thanks a lot for the code!

A follow up question: I also would like to capture the datetime stamp for the remote ftp file before determining whether the file has been transferred before . How could I do that?

Any help would be greatly appreciated!


Wednesday, May 06, 2009 - 12:09:09 PM - raybarley Read The Tip

In the example I used the FtpClientConnection class which is part of the Microsoft.SqlServer namespace.  I did this because it's easier than using the classes in the System.Net namespace of the .Net framework and it met my needs.  Unfortunately FtpClientConnection doesn't support retrieving the datetime of a file on an FTP server.

One option would be to use the FtpWebRequest and FtpWebResponse classes in the System.Net namespace of the ,Net framework. 

 


Wednesday, May 06, 2009 - 2:20:38 PM - Shirl Read The Tip

raybarley,

Thank you so much for your quick response!

I guess that I would have to learn vb.net in order to use those two Ftp classes you mentioned above. So I guess I'll settle with the easy way out for now.

Sorry I have other follow up questions:

After I get the listing, I will download those files who are not already in my history table. Those files are text files and look something like this:

field1:abc
field2:efg   field3:xxx
junkline

field4:hij    field5:abcde
more lines
more lines

I need to quickly glance the file to see if "field5 has the value of abcde", if yes, then I need to SFTP those files to another ftp site.

So, how could I do this matching thing in SSIS? Do you by any chance have example for this? And how do I create folder and put files on SFTP site?

Thanks you so much!


Tuesday, June 23, 2009 - 11:55:23 AM - Gkk Read The Tip

Thanks. There is little twist in my case. I want to receive files with selected names to my local server. Example. I have remote server and folder, let's say Full. Inside full folder there will be multiple files with different names such as A,A1,A2, A3, B, B1, C,D1,D2,D3,D4, Q,Q1,Q2,Q3.....and so one. Now I only want to FTP files Q,Q1,Q2,Q3.........

How can I do that?...Do u think selecting from the table would help or is there extra piece of code that I need to add to do this.

My problem is I am very poor with .NET coding

thanks


Tuesday, June 23, 2009 - 12:35:04 PM - raybarley Read The Tip

If you want to ftp specific files you could put your list of files in a table then join that table with the staging table and that will tell you which of the files you want are actually sitting on the ftp server.  You could put this query in an Execute SQL task which would save the query result set in a package variable, then use a Foreach Loop container to iterate through the result set and ftp each file.

For an example of how to use the Foreach Loop container as I describe above take a look at this tip: http://www.mssqltips.com/tip.asp?tip=1504

Just look at the parts of the tip that talk about the Get Batch List and  Process Batch Loop.

 


Tuesday, October 19, 2010 - 9:54:37 AM - Praneeth Read The Tip
Mr Ray You saved my day.Thanks a lot.  I have no scripting knowledge, this thing greatly helped.


Tuesday, February 14, 2012 - 3:57:03 PM - wanna_learn Read The Tip
Ray, I am new to SSIS and am trying to use your project in MSSQL Server 2008 to get a list of ftp files from our serer. I tried to migrate your project to 2008 but am getting some errors and don't know how to make this work. Any insights would help greatly!!! Error 0xc0016016: : Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

Tuesday, February 14, 2012 - 4:34:06 PM - Raymond Barley Read The Tip
Check the ProtectionLevel package property and make sure it's set to DontSaveSensitive. More details here: http://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/

Wednesday, September 05, 2012 - 7:58:42 PM - dia Read The Tip

where has this article been all my life!

thank you.


Tuesday, December 04, 2012 - 9:45:14 AM - Don Read The Tip

I would like to execute this package by clicking on the package rather than at the command line.  Currently when I try to fill in the parameters in the FTP Connection Manager they do not remain when I close the window.  I have also tried to add the user credentials (username, password) in the Script Task :

ftp.ServerUserName = "adsfasdf"
ftp.ServerPassword = "asdf"

but I continue to get the following error message:

[Connection manager "ftp.myftpsite.net"] Error: An error occurred in the requested FTP operation. Detailed error description: The filename, directory name, or volume label syntax is incorrect.
.
thanks,

don


Tuesday, December 04, 2012 - 1:14:35 PM - Ray Barley Read The Tip

Have you successfully connected to the ftp server using the same user, password, etc. from other than the SSIS package?  Just want to make sure that the values you're using do actually work.

What is your SSIS package protection level?  I assume to retain the ftp user and password it has to be one of the "Encrypt" options or sever storage.  If it's DontSaveSensitive then the user and password won't be saved.  See http://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/ for details.


Thursday, December 06, 2012 - 11:42:26 AM - Don Read The Tip

I have connected successfully to the ftp server from other than the SSIS package.  I notice that when I click on the ftp connection manager and type in the ftp server name in the properties window and then click on another object (a transformation) and then go back and click on the ftp connection manager again the ServerName property is blanked out.  It seems my typed value does not stay in the ServerName box.

 

thanks,

don


Tuesday, January 15, 2013 - 1:37:45 PM - CK Read The Tip

Ray,

Thanks for the nice tip. I also have some issues when I try it on in 2008R2.

Is it possible that you could update the project for SQL Server/SSIS 2008R2 and put the files for us to download to play with? When I open the solution in R2, I get a couple of upgrade errors. When I run the package, I have errors complaining the FTP connection manager:

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

Yes, I set the protection level to DoNotSaveSensitive. Also the ftp server is a good one. But, the same symptom as in Don's post.
.


Tuesday, January 15, 2013 - 8:35:29 PM - Raymond Barley Read The Tip

I don't have an instance of SQL Server 2008 so I opened the project with SQL Server Data Tools (i.e. SQL Server 2012).  The Visual Studio Conversion Wizard runs then the SSIS Package Upgrade Wizard runs.  The wizard reports 1 error:

Error 0xc0016016: GetFtpFileListing: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

I set the PackageProtectionLevel property to DontSaveSensitive, saved the package, set the PackageProtectionLevel property to EncryptSensitiveWithUserKey, entered the server name in the FTP connection manager, saved the package then closed it.  When I open the package the server name is gone.  The only work around I can think of is to try using the SQL Server Package Configuration and set the Server name that way.  You can take a look at this tip for details on SQL Server Package Configuration: http://www.mssqltips.com/sqlservertip/1405/sql-server-integration-services-ssis-package-configuration/

The wizard also shows this warning: Warning    2    Warning loading GetFtpFileListing.dtsx: Found SQL Server Integration Services 2005 Script Task "ScriptTask_3bb2a20ac53347af8d0c7172d12e7aa7" that requires migration!    E:\mssqltips\SSIS_FTP\GetFtpFileListing.dtsx    To fix this simply edit the Script task, click the Edit Script button, save the script, close the script editor and click OK to close the Script Task Editor.  Save the package. 


Wednesday, January 16, 2013 - 2:52:22 PM - CK Read The Tip

Thanks for the prompt reply, Ray. However, I still could not make it work with XML-type Package Configuration, even harded-code the value for the password for the ftp server in the xml file.

Is it possibe that you develop a new package with SSIS 2012. I think the topic is important and there is a need to download a bunch files from a ftp server with newer version of SSIS tools for some users.

Thanks again for the contributions.


Tuesday, February 19, 2013 - 9:25:50 AM - Ivan Read The Tip

Thank you all for your information.

However, how can i create an ssis project that extracts files from an FTP location continously on a daily basis, creates a column on the stagging_table which stores the distinct file name per row entered.Considering it in mind that no duplicate entries are registered.

Thank you very much

I appreciate your contribution.


Tuesday, February 19, 2013 - 1:15:11 PM - Raymond Barley Read The Tip

Check this tip which just came out: http://www.mssqltips.com/sqlservertip/2884/sql-server-integration-services-ssis-ftp-task-for-data-exchange/ It goes thru how to use the ftp task.

You can create a SQL Agent job that executes the SSIS package based on some schedule.  If you really want to continuously poll for files to download then you can use the approach in this tip to query for the list of files, check if the file has been previously downloaded, then use the ftp task when you get a file to be downloaded.  To accomplish the polling you can use a for loop container ( http://msdn.microsoft.com/en-us/library/ms139956.aspx ); to add a pause in the loop use an execute sql task with the t-sql WAITFOR


Monday, July 15, 2013 - 5:15:18 AM - ighi Read The Tip

Thanks, this example worked perfectly 


Tuesday, November 12, 2013 - 1:10:27 PM - Liam Read The Tip

Excellent, thank you



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.