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] + “
6. 

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