Set FTP password in SSIS

undoubtedly, you’re reading this because you’ve discovered that SQL Server Integration Services (as of SSIS 2008) will not allow you to set the password of an FTP connection through expressions. Fortunately, there is an easy workaround, that requires a simple Script Task. While not as simple as native expression support, it’s darn close. I’ve included C# code, but you may need to adapt to VB.Net if that’s your preferred flavor.

1. Ensure a string variable is setup with the username/password. For this demo, I’m using the name, “FTPUserName”/“FTPPassword”
2. Add a Script Task to your package
3. Edit the task
4. On the Script page, click the elipsis for ReadOnlyVariables and check the box for User::FTPUserName and User::FTPPassword.
5. Click the “Edit Script…” button
6. Change your entry point (Main, by default) to look like the below code. Save, close, and hit OK.

public void Main()
{
    ConnectionManager FTPConn = Dts.Connections["FTP Connection"];
    FTPConn.Properties["ServerUserName"].SetValue(FTPConn, Dts.Variables["FTPUserName"].Value);
    FTPConn.Properties["ServerPassword"].SetValue(FTPConn, Dts.Variables["FTPPassword"].Value);

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

A couple notes:

• Ensure you update lines 4-5 to reflect the actual connection name. My example uses the name FTPServer.
• Just to reiterate, my password is stored in the variable name FTPPassword. If yours is different make this change on line 5.

That’s it. Make sure you’ve got this task being executed before your actual FTP task and everything should work fine. Cheers!