Jakub Szymaszek links to two articles on using SSIS with an Always Encrypted database.
The SQL Server 2016 Always-Encrypted feature is only supported by the ADO.NET provider currently. It is not supported by the OleDB provider and therefore any OleDB-provider-related transformation tasks such as Fuzzy Lookup will not support Always Encrypted feature.
In the “Execute SQL Task”, parameter binding for some encrypted SQL types is not supported, because of data type conversion limitations in Always Encrypted. The
unsupported types are money, smallmoney, smalldatetime, UniqueIndentifier, DatatimeOffset, time and date.
Add an ADO NET source connect to the table “Customers” (please ref to here get more detail about how to use ADO NET Source to connect encrypted table).
Then create a cache connection manager “Customer Cache” and set the column information as below:
Based on article #2, it looks like you can’t simply use a Lookup transformation on an Always Encrypted column; you need to pull the results into cache first and then query the cache. That’s not exactly difficult, but if you have an encrypted column, make sure you’re not writing those columns out in plaintext because of the cache option you selected.