Get a List of Files on an FTP Server and then process with Foreach Loop Container

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

Advertisements

Dynamically set secure FTP connection string and password

Note: This walkthrough is specific to the Secure FTP component available in TaskFactory. TaskFactory is a product offered by Pragmatic Works consisting of 40+ Custom SSIS components for development.

Recently I worked with a client who had an interesting business problem. This client processed documents daily for hundreds of clients and then uploaded these files to each client’s Secure FTP site. They needed to set up the Secure FTP component so that they could dynamically change the connection based on the file being processed and uploaded. In this walkthrough I will explain how the connection string for your FTP Site can be set dynamically.

  • Part 1 – Building the connection string.
  • Part 2 – Setting an expression on the TF Secure FTP Connection Manager

(Note: This tutorial is modifying the connection string of an existing Task Factory sFTP connection manager. If you want to follow along please set up a basic TF Secure FTP task and create a connection manager.)

Part 1 – Building the TF Secure FTP connection string.

There are 18 parts to the connection string for the Secure FTP connection. However for this tutorial we are only concerned with 5 of the 18 sections, we will leave the other sections set to the default settings. What are the 5 sections we are concerned with? I’m glad you asked!

  1. Connection Type
  • 1 = SFTP – SSH FTP
  • 2 = FTPS – FTP over implicit TLS or SSL
  • 3 = FTPES – FTP over explicit TLS or SSL
  • 4 = FTP
  1. Host
  2. User Name
  3. Password
  4. Port

Ok let’s get started. For this example I created 5 variables, all 5 variables have a data type of String. For demonstration purposes I hardcoded the values for each variable. For the case referenced above the client populated these variables from a SQL Table that stored the connection information.

  1. Create the five variables in the screenshot above.
  2. Add these variables to the connection string. (Connection String Text below)
  3. If you used the same variable names as this tutorial you can simply copy the connection string below. (Section A)
  4. Here is a screenshot of where we changed our connection string.
  5. I replaced the hard coded value in the connection string with ” + @[User::VariableName] + “

Section A

“ConnectionType=” + @[User::strConnectionType] + “;Host=” + @[User::strHost] + “;Port=” + @[User::strPort] + “;Username=” + @[User::strUserName] +”;Password=” + @[User::strPassword] + “;Timeout=60;DefaultRemoteDirectory=;UseProxy=False;ReuseConnection=False;ProxyType=0; ProxyHost=;ProxyPort=0;ProxyUsername=;UseBinaryTransfer=True;UsePassiveMode=True; IgnoreServerIP=True;TransferBufferSize=0;SSLVersion=2;”

Part 2 – Setting an expression on the TF Secure FTP Connection Manager

  1. Under Connection Managers highlight your FTP Connection and hit F4 to open the properties window.

     

  2. In the properties window highlight “Expressions” and click the ellipsis button on the right.

     

  3. In the Property Expressions Editor choose ConnectionString for the Property.
  4. Next click the Ellipsis on the left to open up the Expression Builder.
  5. Now copy and paste the connection string into the Expression box.

     

  6. Evaluate the expression to verify it works.
  7. Click ok to close the Expression Builder.
  8. Click ok again to close the Property Expressions Editor.
  9. All Done!

Conclusion: Whether you are uploading files to multiple FTP sites or just want to set the Password dynamically because it changes periodically this method will help. Thanks for looking!

Follow me on twitter! @MitchellSQL