My lookup data is in a csv file, not a table. Is there a way to get the Lookup transformation to use the csv file as the source 'table'? Obviously the alternative is to load the file into a SQL Server table and use that, but I want to keep it simple if possible.
The lookup component can only use OLE DB sources. You might be able to construct a query with OPENROWSET to read the text file via OLE DB, but that doesn't seem like the simple solution. You might also be able to use a Merge Join in Left Outer mode instead of a Lookup, which would let your source the right side from a flat file. You may have to add some sorting steps, though.|||
The technique that I prefer for this situation is to have a separate package (or a separate data flow within the same package) that loads the lookup data from the source CSV into a lookup table in SQL Server (or whatever is your RDBMS of choice) and then perform the lookup in the "main" package against the database table version of the lookup data. If you ensure that the CSV to Table package is always executed first, this should solve the problem neatly.
Although this is not quite as simple as the ideal situation where the Lookup transform supported non-OLE DB data sources, it is much simpler (and much better performing) than any other alternative that I can think of.
No comments:
Post a Comment