http://sqlblogcasts.com/blogs/jay_uttam/default.aspxenCommunityServer 2007.1 (Build: 20917.1142)http://sqlblogcasts.com/blogs/jay_uttam/archive/2008/02/17/range-lookup-in-ssis.aspx#12934Wed, 27 Jan 2010 20:20:44 GMTfa8c4e8e-46a3-4193-8264-2c1a9cb3475d:12934CozyRoc<p>There is solution based on the third-party commercial CozyRoc SSIS+ library. CozyRoc has implemented data flow destination script, which creates memory-efficient range dictionary object. The dictionary object can then be used in CozyRoc Lookup Plus component. For more information and demonstration how to use the script, check here:</p> <p><a rel="nofollow" target="_new" href="http://www.cozyroc.com/script/range-dictionary-destination">www.cozyroc.com/.../range-dictionary-destination</a></p> <img src="http://sqlblogcasts.com/aggbug.aspx?PostID=12934" width="1" height="1">http://sqlblogcasts.com/blogs/jay_uttam/archive/2008/02/17/range-lookup-in-ssis.aspx#7566Wed, 20 Feb 2008 12:21:21 GMTfa8c4e8e-46a3-4193-8264-2c1a9cb3475d:7566simonsabin<p>I've found to get the best performance you need to use a script component and load your dataset into memory (usual caveats about available memory etc) and then you can use the binary search to find the value you want. This will be far quicker than having to make a database call for every unique value that comes along. </p> <img src="http://sqlblogcasts.com/aggbug.aspx?PostID=7566" width="1" height="1">