Flatfile as a Lookup in SSIS

Question

Flatfile as a Lookup in SSIS

bpradeep

SSC Veteran

Points: 249
More actions
October 10, 2009 at 10:29 pm

#136447

is it possible to use flat file as a lookup file.?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 1

Unfortunately not with SSIS 2005. In SQL 2008 however, this is possible with the cache transform / connection manager and the new lookup component. In 2005, for this type of behaviour you need to use a merge join component.

HTH

Kindest Regards,

Frank Bazan

Paul Williams SSCommitted Points: 1930 More actions · Answer 2

While Frank is correct in saying that 2008 can accommodate this through the use of the cache transform, I believe it is possible in 2005 through a workaround.

While the lookup in 2005 can only use an OLEDB connection manager you could potentially do this to populate your lookup from a text file:

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

I haven't actually tried this to prove that it works though, but I do recall this as a suggested workaround in the past.

Paul R Williams.

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

bmraja77 (10/10/2009)
is it possible to use flat file as a lookup file.?

If you can use third-party components, check the commercial CozyRoc Lookup component. You can load the flat file in dictionary object using the Dictionary Destination script and then use the dictionary object in the lookup component.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/