SSIS FTP Task Password (and other) Problems

Originally posted on SQL Server Central, but posting it here as well for my future reference. :)

I've had some problems with the SSIS FTP Task where it does not seem to make the connections correctly. I keep getting the error "Password is invalid" and all sorts of errors.

I don't really like to use the script task component because of it just creates another area of potential coding error in the project.

But I had to take control of the FTP task functionality myself in order to make it do what I wanted it to do.

I realised that when the FTP Task does a listing, the unix box returns not only the file name but the timestamp as well.
Eg,
Problem 1
directory on the unix box
/files
File1.trg
File2.trg

When listing from FTP Task: remote directory = /files
"10:00 File1.trg"
"12:00 File2.trg"

Now, when the ftp task tries to get these files, its not found, or throws some error.

Problem 2
Passwords. Using a batch file to script the FTP is not a good solution, because at the end of the day, your password is clearly visible in plaintext in the script. Not a good solution. Using the package configuration file (*.dtsConfig) is useless as it too stores passwords in plaintext (till now, ssis hasn't implemented encrypted configuration files). Don't even think of using enterprise library with SSIS, you'll run into major headaches.

Solution
I've since given up on using the FTP task for my FTP, but we can still use SSIS's FTP plumbing and with some extra coding, make a properly platform independent and secure FTP function.

I've removed the error checking and variable assignment to make things easier to read.
Steps:
1. Create a FTP Connection Manager in your SSIS Designer 'RightClick in Connections - New Connection... - FTP'.
2. Create a variable to store the password.
Setup the package to use configuration file to store the password variable (and other information you need).
3. Fill in the FTP information as needed. 3.
4. Write a simple .net application that does string encryption. I won't put code for this here. You can google how to do this easily.
5. Use this tool to navigate the xpath of the dtsConfig file to password variable's value and encrypt it. This way, the password is not plaintext on the dtsconfig file.
6. Create a script task to take the password variable's (as readwrite) value and 'decrypt' it, remember to use whatever method was used to encrypt it in your separate .net app. Assign the decrypted password back to itself.
6. Create a script task to do the FTP functionality.
Public Sub Main()
'TODO: assign variables here...
dim password as string = dts.variables("vPassword").value.tostring 'the decrypted password

'Get instance of the connection manager.
Dim cm As ConnectionManager = Dts.Connections("FTPConnMgr")
'Set the password property to the decrypted password
cm.Properties("ServerPassword").SetValue(cm, password)

'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

'Connect to the ftp server
ftp.Connect()

ftp.SetWorkingDirectory(remoteDir) 'set the remote directory

Dim files(0) As String
files(0) = fileToGet 'eg. File1.trg

'Get the file
ftp.ReceiveFiles(files, localDir, True, True)

' Close the ftp connection
ftp.Close()


Dts.Events.FireInformation(0, context, "File " + fileToGet + " retrieved successfully.", Nothing, Nothing, True)
Dts.TaskResult = Dts.Results.Success

End Sub

So there you go. An ssis package that decrypts a encrypted password on a dtsconfig file, decrypts it at runtime, and doesn't use batch files that expose the password.

If you'd like to know how to do a batch get of all files in a directory, i can show in another post. Whenever you need to change the password, just use that separate .net app to modify the dtsconfig file. Nothing needs to be done on the package.