Today we are going to address a very common FTP question: “How can I get a list of the files that are on my FTP Server?”

This is a task that can be accomplished very easily with the Task Factory Secure FTP component and this walk through will provide step by step directions. If you are interested in Task Factory and the more than 40 tasks you can find the free trial here:

Here we will use the “Get list of files” option and store all the files for a given directory into an object variable. We will use a Foreach loop container to iterate through the object variable and then an Execute SQL task to write each file name to a table in our database. This walkthrough is broken down into three sections.

• Part 1 – Setting up the Secure FTP component to get List of Files
• Part 2 – Setting up the For Each Loop to read the the object variable
• Part 3 – (Optional) Setting up the Execute SQL Task to write the file names to a table.

Part 1 – Configuring the Secure FTP site to Get List of Files from server.

1. Start a new package and drag the Secure FTP task into your control flow from the toolbox.
2. Next right click on the Secure FTP Task and click edit.
3. Steps 4-7 below are in screenshot 1
4. What will this task do? Select “Get list of files” from the drop down menu.
5. For SFTP Connection Manager either choose an existing connection manager or create a new one if don’t have one set up.
6. For your Directory location on server you have two options:
   1. First you can choose to click the ellipsis button and hard code the directory location.
   2. Second you can choose to store the directory location in a variable
7. Next create an object variable to store the list of file names in from the server. (objFilesList)

Screenshot 1

Part 2 – Configuring the For Each Loop to iterate through the object variable

1. Pull a Foreach Loop container into the Control Flow and drag the precedence constraint from the Secure FTP task to the Foreach loop container.
2. Steps 3-6 can be found in Screenshot 2
3. Open the For Each container and click on the Collection tab.
4. For “Enumerator” choose “Foreach From Variable Enumerator” from the drop down.
5. Next select the object variable you specified in the Secure FTP component. (objFilesList)
6. Finally click the Variable Mappings tab.

Screenshot 2

1. On the Variable Mappings tab select a string variable to store the file name in. (strFileName)
2. Make sure Index is set to 0. (Screenshot 3)
3. Click Ok.

Screenshot 3

Optional: (This section simply shows how to map variables in an Execute SQL Task and helps with visualization of each iteration of the Foreach Loop through the list of files on our FTP.)

Part 3 – Setting up the Execute SQL Task to read the string variable

(This section has been added for completeness and is fully optional.)

Note: For this example I have created a simple table with four columns:

CREATE TABLE [dbo].[FilesOnSftp](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Directory] [varchar](50) NULL,

[FileName] [varchar](50) NULL,

[DateLoaded] [date] NULL)

1. Pull an Execute SQL Task from the toolbox into the Foreach Loop container.
2. Create a new variable called strSftpDirectory and give it a value of /

   

3. Right click on the Execute SQL Task and click Edit to bring up the Editor
4. For “Connection” choose the connection manager to your database.
5. Click and highlight “SQLStatement” and then click the Ellipsis button that appears on the right.

Screenshot 4

1. Clicking the Ellipsis button will give you a pop up window, this is where we enter our Insert SQL Statement. Here is the syntax I used if you are following along with the tutorial:

   INSERT INTO dbo.FilesOnSftp

([Directory], [FileName], [DateLoaded])

VALUES(?, ?,GETDATE())

1. Now its time to map the parameters. Click on the Parameter Mapping tab. (Screenshot 5)
2. Click the Add button and then select “strSftpDirectory” for Variable Name.
3. For data type choose Varchar and change the Parameter Name to 0.
4. Click the Add button a second time and select strFileName for the Variable Name.
5. For data type choose Varchar and change the Parameter Name to 1. Click ok to save changes.

   Screenshot 5