SSIS: Set FTP Connection and Credential Info From a Variable

SSIS FTP Connection Manager

The Problem With Setting Up SSIS FTP Connections

Your ETL environment is set up to follow a proper development lifecycle. Your SSIS ETL packages use package configurations and connect to systems with different connection strings depending on whether you’re in your development, staging, or production environments. You try to set up your FTP connections to work the same way, and you run into some issues. Maybe you tried to use a URL-style FTP path in the connection string field to get all of the credential information in there, and found it doesn’t work. Maybe you tried to use SSIS expressions but found some of the credential fields aren’t exposed as expression properties. Maybe you’re starting to get frustrated trying all of these things that aren’t working?

A Solution For Setting Up SSIS FTP Connections

Don’t despair my friends! The good news is that there is a method for setting up SSIS FTP connections in that dynamic variable-driven way that you want them to work. It’s simple too, once you know how to do it. All it takes is a script task and a a few lines of code.

First, for the setup. How do you like to save your FTP connection information? I like to save my connection information as a single string in my package configurations, just like I do with other SSIS connection types. This is an example of how I save a FTP connection string:

Server=myftpserver.com;Port=21;User=MyUsername;Pwd=MyPassword

Note that Pwd is the last element of the connection string – I do that on purpose because Pwd is likely the only element of the connection string that could include a semicolon as part of its data, and the way you will see that I parse the string later it will still parse correctly when the Pwd field is the last field.

Now, let’s assume we’ve pulled our sample connection string into a SSIS package variable called “FTPConnStr”, and we have a FTP connection manager set up in our package called “FTP”. We can use the following .NET code in a SSIS script task to properly set up our connection manager:


// Remember to include this additional using directive in your code to use the NameValueCollection.
// using System.Collections.Specialized;

public void Main() {
    // Get FTP Connection String from package variable.
    string FTPConnS = Dts.Variables["FTPConnStr"].Value.ToString();

    // Set up a Name Value collection to hold each of the FTP Connection parts.
    NameValueCollection FTPConnColl = new NameValueCollection();

    // Populate the Name Value collection from the Connection String variable.
    string[] aryStrings = FTPConnS.Split(new char[] {';'}, 4);
    string[] nameAndValue;
    foreach (string s in aryStrings) {
        nameAndValue = s.Split(new char[] {'='}, 2);
        FTPConnColl.Add(nameAndValue[0], nameAndValue[1]);
    }

    // Connect to our FTP connection manager.
    ConnectionManager FTPConn = Dts.Connections["FTP"];

    // Set our FTP Connection manager properties from the FTP Name Value Collection.
    FTPConn.Properties["ServerName"].SetValue(FTPConn, FTPConnColl["Server"]);
    FTPConn.Properties["ServerPort"].SetValue(FTPConn, FTPConnColl["Port"]);
    FTPConn.Properties["ServerUserName"].SetValue(FTPConn, FTPConnColl["User"]);
    FTPConn.Properties["ServerPassword"].SetValue(FTPConn, FTPConnColl["Pwd"]);

    Dts.TaskResult = (int)ScriptResults.Success;
}

See, there’s really not much to the code. First, because the connection information is saved as a single string, we parse that string out to its components. Then we connect to the SSIS FTP connection manager and update it with those connection data components.

Conclusion

That’s it, a simple SSIS script task method for setting your FTP connection and credential information dynamically from a variable. Want to see an example of it working? Have a look at this Sample SSIS FTP package, where the book Alice in Wonderland is download from Project Gutenberg. You’ll see the code from this article being used in the first script task of the package.

Have any questions or comments on the methods described here for setting up FTP connections in SSIS? As always, feel free to share.

Leave a Reply

Your email address will not be published. Required fields are marked *