Hi,
I have a requirement to loop through several SFTP sites, grab files, and then perform various actions on them in SSIS. All SFTP connection information will live in a table in a SQL Server 2016 database. I've got the overall structure of the SSIS package working well. However, I'm now at the point where I want to make sure sensitive information, like the passwords to access the SFTP sites, is properly encrypted - in the database and when in use in SSIS.
I'm a novice when it comes to encryption, but my initial thought was to encrypt the appropriate database columns using "always encrypted" and pass them into SSIS parameters marked as "sensitive". However, I can't seem to get this to work as expected. Has anyone had luck doing this? Is there a different approach that makes more sense?