SSIS: Lookup from Text Files


Ever want to do a lookup on a text file without putting it in a database or using a merge join?


Grant from the Project REAL team found a great nugget:


Openrowset now allows the BULK clause to be specified which accepts a filename and format specification. An OLE-DB driver for flat-files or XML would also do the job but this is a nice work-around for the native stack. The only difficulty is getting your head around the format for the .fmt file but after that it ‘just works’:


    select *
    from openrowset(bulk N’c:\temp\test.txt’, formatfile = ‘c:\temp\test.fmt’) as x


thanks, Grant!


 


Comments (2)

  1. roy ashbrook says:

    snipped from here . Openrowset now allows the BULK clause to be specified which accepts a filename and