Lookup transformation with Always Encrypted
Published Mar 25 2019 03:59 PM 1,987 Views
Copper Contributor
First published on MSDN on Dec 17, 2015

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.

Version history
Last update:
‎Mar 25 2019 03:59 PM
Updated by: