is it possible to join flaffile source and oledb source using mergejoin transformation in ssis
hi all i a new to ssis my question is how to join between flatfile source and oledb source table by using merge join in ssis data flow transformation
March 31st, 2011 12:59am

add tow different source in data flow a flat file source , and an oledb source add two sort transformations after each of sources, and sort them by joining keys, then connect output of sort transforms as input of merge join transformations, and select joining key(s). That's all. http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
March 31st, 2011 1:02am

To expand on the answer of Reza: it is probably more efficient to sort the data from the OLE DB source by using the ORDER BY clause in the select statement than using the SORT component, as that is a blocking component. You can mark the data as sorted in the advanced editor of the OLE DB source. http://www.ssistalk.com/2009/09/17/ssis-avoiding-the-sort-components/ However, you'll still need the SORT component for the flat file source.MCTS, MCITP - Please mark posts as answered where appropriate.
March 31st, 2011 2:01am

To expand on the answer of Koen: but be careful with joins on string fields, because SSIS and SQL server sort differently. And for very large files it might just be faster to first load them into a table and do the sort (and optional also the join) in sql server.Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
Free Windows Admin Tool Kit Click here and download it now
March 31st, 2011 2:16am

Good point on the collations, Joost, and thanks for the reference. And indeed, if the flat file is more than a Gigabyte in size, I would certainly avoid the SORT component, as all the data has to be loaded into memory.MCTS, MCITP - Please mark posts as answered where appropriate.
March 31st, 2011 2:22am

Use Isorted option source itself instead of sort transformation./R.
Free Windows Admin Tool Kit Click here and download it now
March 31st, 2011 4:21am

Use Isorted option source itself instead of sort transformation. Will that work on the Flat File source, if the data in the file isn't sorted? I don't believe so...MCTS, MCITP - Please mark posts as answered where appropriate.
March 31st, 2011 4:22am

In advance Editor should be there./R.
Free Windows Admin Tool Kit Click here and download it now
March 31st, 2011 4:25am

In advance Editor should be there. /R. Let me rephrase my question: what sense would it make if you indicate in the advanced editor that the incoming data is sorted, when the source file isn't sorted at all? Doing this will cause the MERGE JOIN to give incorrect results.MCTS, MCITP - Please mark posts as answered where appropriate.
March 31st, 2011 4:33am

can you us give more information about your requrirement??-- Sathish
Free Windows Admin Tool Kit Click here and download it now
March 31st, 2011 4:57am

In advance Editor should be there. /R. Let me rephrase my question: what sense would it make if you indicate in the advanced editor that the incoming data is sorted, when the source file isn't sorted at all? Doing this will cause the MERGE JOIN to give incorrect results. MCTS, MCITP - Please mark posts as answered where appropriate. Agree! Just saying your file is sorted in the advanced editor wont work unless your file is really sorted. But all the working options are already posted above... Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
March 31st, 2011 7:19am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics