There might be times when developing a SQL Server Integration Services (SSIS) package that you want to perform a lookup in order to supplement or validate the data in you data flow. A lookup lets you access data related to your current dataset without having to create a special structure to support that access. The lookup transformation lets you to access a references dataset either through an OLE DB connection manager or through a Cache connection manager.
As the Always Encrypted only supports ADO.Net Data provider and ODBC provider, it is not supported by OleDB Provider. If your package used the OLE DB connection manager to access the encrypted table, it will not work. We will provide a solution to use the encrypted table as references dataset in your SSIS package in this blog. It only applies to the full cache mode.
Assume there is an insurance company provides insurance services for multiple companies. They are storing the reimbursement transaction in a table called “Reimbursement”. There is another table “Customers” store the information for the customer. The insurance company want to know the total reimbursement amount of each company. So they need to use lookup transformation to add the company id to the “Reimbursement” table.
The schema of these two tables are as below:
In the original package, we only need one data flow, it is like this.
With the support of Always Encrypted, this insurance company want to encrypt the sensitive column [Customers].[SSN] and [Reimbursement][CustermerSSN], as the OleDB provider is not supported by Always Encrypted, we need to update the SSIS package.
We need two data flow tasks in the new package
Data flow 1 – Load data from lookup table “Customers” into the cache
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:
In the Cache Transformation Editor set the cache connection manger to the “Customer Cache”
Data flow 2 – Get company information for the reimbursement transaction
Add the ADO NET source to connect the “Reimbursement” table. In the lookup transformation set the connection type to Cache connection manager.
Select the “Customer Cache” in the connection tab
Now, you can ran your package and get the company information for the each reimbursement transaction.