SSIS FTP Task - Code to set the password
If you are having problems setting the password to the FTP task in SSIS, then the likelyhood is you are trying to use an expression to set it (can’t be done); being caught out by clicking [OK] (finger trouble) in the FTP connection manager editor, or you’re struggling with the encryption settings (a long subject) for your package.
I don’t want to enter into discussion here on the encryption level you use (I will discuss that in another post some other time), merely this post just contains some simple code that you can use to set the password with a script component placed before the FTP task.
Amazon Link: Hands-On Microsoft SQL Server 2005 Integration Services
It takes the value of a variable “FTPPassword” and uses it to set the FTP connection manager property “ServerPassword”.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim FTPConnectionManager As ConnectionManager
'Set variable to an existing connection manager
FTPConnectionManager = Dts.Connections("FTP Server")
'Set connection manager property "ServerPassword"
FTPConnectionManager.Properties("ServerPassword").SetValue(FTPConnectionManager, Dts.Variables("FTPPassword").Value)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Cheers
Frank
June 10th, 2008 at 1:06 pm
Thanks for this. It worked like a charm!
July 10th, 2008 at 9:14 am
SSIS prevents you from setting the value of ServerPassword using an expression (i.e. when you look in the expressions window for an FTP Connection, the property “ServerPassword” is not available in the properties drop down).
Presumably this was Microsoft’s way of increasing security; because if you’re using an expression to set the ServerPassword you’re probably planning to specify the password in a variable (which is plain text).
This is just plain annoying. Security should be left to the user and not foisted on them. There are many instances where the visibility of the password in the local environment would not be an issue.
That said Frank’s solution works great. Thank you Frank
July 10th, 2008 at 12:52 pm
I couldn’t agree with you more
Especially where FTP is concerned - there’s little point getting all security conscious when no matter where the password comes from, it will be sent over the network in clear text. A simple packet sniffer will tell you the password anyhow!
It is possible to configure the connection manager’s password directly, but you have to be careful with your package encryption settings. If you are saving your configurations to xml file, then a package saved with encryption level “DontSaveSensitive” is going to wipe out your password. What you then have to do is type it into the .dtsConfig file after you’ve saved the package, but before you deploy it.
Personally, I prefer to set all my component properties from variables and configure the variables instead. It makes debugging much easier, because you can see the values at run-time.
Cheers
Frank
November 4th, 2008 at 2:19 pm
Hello,
Great script. Shame it has to be done that way but there you are. Thanks you’ve saved me ages!
Fran
October 30th, 2009 at 6:59 pm
Thanks for the script! Saved me some time today
February 5th, 2010 at 11:36 am
Hi guys,
I just can’t get this to work at all. I have exactly the same requirement as the millions of other people who have all resolved it using the above. It just doesn’t work in my solution!!!!!!!!!!!
It’s ok though as I’m about to take a chainsaw to my laptop. It has to be something obvious!
February 5th, 2010 at 11:58 am
UPDATE:
the prefix to my variable “user::” seems to have been the problem. I removed this and it worked.
That prefix has never caused problems in the past, shame i was looking forward to giving it a damn good thrashing!
February 5th, 2010 at 12:30 pm
Hey Chris,
Glad you got it working… hope the laptop survives another day
Cheers