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!