Thursday, October 18, 2012

Fuzzy lookup issues in 32 bit runtime

This post is regarding an issue I found while working with SSIS 2005/2008 fuzzy lookup using 32 bit runtime on a 64 bit instance.
Recently I was using fuzzy lookup in SSIS package data flow as a part of my requirement in SSIS 2008 and while running the package I found that it launches SQL Dumper and then packages execution gets frozen on fuzzy lookup task which remains in yellow icon until we stop the package. I could even reproduce this issue on SSIS 2005. I tried out various things and finally found out a workaround for this. On going to project properties and configuring it to run in 64 bit (turn property value of Run64bitRuntime from False to True) seems to have solved this issue.

 
I went ahead and checked this on SSIS 2012 using backward compatible legacy deployment model and this issue seems to have fixed in SSIS 2012.

I searched and also found a connect item explaining this issue

https://connect.microsoft.com/SQLServer/feedback/details/488387/fuzzy-lookup-triggers-sqldumper-even-with-very-small-dataset-with-run64bitruntime-set-to-false

Not sure how one would work in case they've to use a source type which supports only 32 bit provider like Excel 2003 and fuzzy lookup in the same package in SSIS 2008. Might have to use a workaround of cascading packages.
I'm planning to research further on this and will post shortly on my findings on this scenario.