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.

The Phoenix Project

Just wanted to take a second during my lunch break to let you know about a book called “The Phoenix Project”. Back in ancient times, the 1980’s, a man named  named Eli Goldratt wrote a book entitled “The Goal”. It was about applying a concept called “Theory of Constraints” to the manufacturing process. But this wasn’t a boring textbook, but instead written as a novel. At the time a new concept, in the time since it was published the Theory of Constraints has become integrated into almost every manufacturing operation around the world.

“The Phoenix Project” takes the concepts of The Goal and updates them with new management techniques like Agile and Kanban, then applies them to the crazy world of IT. And just like in The Goal, The Phoenix Project uses the form of a novel to tell the story.

Bill is a newly minted VP of IT for Parts Unlimited. Bill inherits a mess, a project pivotal to the success of the company dubbed The Phoenix Project is over two years late and out of control. Not only that, operations in general is in a shambles. Bill is given just a few months to fix it, or the entire IT division will be outsourced.

Paralleling the goal, a mysterious figure steps in to mentor Bill. Using the Socratic method, he guides Bill in the application of Theory of Constraints, Agile, Kanban, and other techniques to the world of IT. Along the way Bill shifts the organization from a silo model of Dev, Operations, and Projects to the unified model known as DevOps.

So why am I delaying my lunch break (my lovely wife makes a killer cube steak) to blog about this? Well until Midnight, Wednesday April 3rd you can get the book for FREE in Kindle format from Amazon:

I had actually paid good money and loved the book so much I’d already started this review post, now that it’s free I’m happy to share with all of you. It’s not a long book, I read most of it on a Friday evening and finished it up on a Saturday. Since finishing it I’ve begun applying some of the concepts to my personal and professional life with good results. If you happen to run across this post after April 3rd I still recommend getting it, even if you have to pay for it like I did. It is well worth your time and money.

Don’t have a Kindle? No problem, there is free Kindle software for all the tablets; iPad, Android and yes even Surface. No device? There’s also a Kindle app for your PC and Mac. If all else fails, there’s even a cloud reader so you can read your books on any web browser.

If you want to know more about DevOps, the authors (Gene Kim, Kevin Behr, and George Spafford) run a website called IT Revolution at where you can find out more.

This is a great book that should be required reading for everyone in IT. I believe it will have as big an impact to IT as The Goal did to manufacturing. And if you haven’t read The Goal, get it as well, perhaps even read it first. The Phoenix Project makes several references to The Goal, which will make more sense if you read The Goal first. (The Goal is also available as an audio book from Audible, I’m hoping the Phoenix Project will be converted to audio soon.)

Enjoy, now if you’ll excuse me I have a cube steak with my name on it.

Run As Robert

Recently I was a guest on Richard Campbell’s podcast “RunAs Radio”. It was a lot of fun, we talked about Business Intelligence as it’s evolved and what its future might be with technologies such as Hadoop up and coming. You can find the interview at: 

One minor correction, in my bio he misread my “MCTS” certification as an MCT. Just wanted to clarify that point since I’m not an MCT (yet, LOL).