For security reason, the sensitive information must be encrypted, and they should be configurable dynamically (by an ASP.NET application).
so I tried several ways to achieve this,
1. use SSIS Package Configurations to generate an XML config file
It's a convenient way to generate config file. but the passwords are not encrypted (or I don't know how).
2. use Variables and Property Expressions
I can set variables by reading from an external custom xml config file which was content encrypted (read and decrypt the custom config file in a script task). but in a FTP Connection Manager entity, the Password property can not be set via Property Expressions.
Is any way to store password encrypted to an external file?
OK, It was resolved, I try to set Variables from a custom XML config file. Most of Properties of Connection Managers can be set via Property Expressions but password. So I set the password in a Script Task programatically. It will like this:
Public Sub Main()
'a custom class to read my custom config file
Dim c As Config = Config.GetConfig()
Dts.Variables("FtpServerIP").Value = c.Settings("FtpServerIP")
Dts.Variables("FtpServerPort").Value = Convert.ToInt16(c.Settings("FtpServerPort"))
Dts.Variables("FtpServerLogin").Value = c.Settings("FtpServerLogin")
Dts.Variables("FtpServerPassword").Value = MyDecryptMethod( c.Settings("FtpServerPassword") )
Dts.Connections("FTP Server").Properties("ServerPassword").SetValue(Dts.Connections("FTP Server"), Dts.Variables("FtpServerPassword").Value)
Dts.TaskResult = Dts.Results.Success
End Sub
Now I can store and encrypt my password or other sensitive information in a custom XML config file, and change it dynamically. It looks quiet complex, but it's the only way I know.
Any good suggestion?
