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?
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]
, @xml xml
SET NOCOUNT ON;
DELETE FROM [dbo].[stg_FtpFileList]
WHERE [FileType] = @fileType
INSERT INTO [dbo].[stg_FtpFileList] (
,doc.col.value('@name', 'nvarchar(50)') filename
FROM @xml.nodes('//file') doc(col)
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.:
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
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.
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
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.
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, January 15, 2013 - 8:35:29 PM - Raymond Barley
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.
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. .
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.
Tuesday, December 04, 2012 - 1:14:35 PM - Ray Barley
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.
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 :
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,
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/
Tuesday, February 14, 2012 - 3:57:03 PM - wanna_learn
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.
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.
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.
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.