Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Populate the Lookup component cache from a flat file

Its a commonly held belief that the Lookup component cache has to be populated from a relational source.  Well, that's true, but there is a pretty handy workaround. SQL Server 2005 introduced the OPENROWSET(BULK) command which can return the contents of a file as a recordset hence something like the following will successfully populate the Lookup cache:

  • SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt', FORMATFILE = 'c:\test\values.fmt') AS a;

Thanks to Grant Dickinson for letting me know about this little tip. I've actually been meaning to blog about this since he told me about it way back in February 2007 but I never got round to writing a substantial blog post around it. So instead this evening I just thought, to hell with it, just throw SOMETHING up there and have done with it. So here you go.

As I'm sure many of you aware by now, this workaround won't be necessary in SSIS2008 due to the enhanced Lookup functionality.

Hope that helps!

-Jamie

Published Wednesday, March 12, 2008 4:15 AM by jamie.thomson

Comments

 

enhanced said:

March 12, 2008 8:07 AM
 

Trumpi's blog said:

SSIS: Populate the Lookup component cache from a flat file Turn Anonymous Types into IDictionary of values

March 12, 2008 8:33 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems