SSIS tip for Lookup Transformations

The Lookup Transformation is a cornerstone of almost any SSIS package. The vast majority of packages that load Fact tables use Lookups extensively. Many of these lookups reference the same tables over and over.

As we all (hopefully) know by now, opting to lookup against a table is akin to doing a SELECT *. Best practices guide you to only select the columns you really need for a lookup, typically the surrogate key and business key. Because I often reference the same tables over and over, I’ve taken to keeping all my frequently referenced tables in a single SQL file.

I typically store all my projects in a “Projects” folder off my root directory (aka C:\). Under it I create a folder for each project I work on. Within there I create a folder simply called SQL, where I store some SQL scripts. Some are just temporary as I work through issues, or as in this case a good place to store my commonly used lookups. It will wind up looking something like:

-- Employee
SELECT DimEmployeeId, EmployeeBusinessKey
  FROM DimEmployee

-- Company
SELECT DimCompanyId, CompanyBusinessKey
  FROM DimCompany

-- Office
SELECT DimOfficeId, OfficeBusinessKey
  FROM DimOffice

-- More here

That’s a very generic example, but you get the idea. Simple, but very handy.