I have 1000 records in table 1 and 10,000 records in table 2. Which one does you suggest and explain the reason. Use a Join Transformation and combine two sourcesUse Source Qualifier write a join query and get the resultTake the Lesser data set as lookup and larger as source and perform the resulting operation.

Hi kushal patel The best option is 2, Use a Source Qualifier and write a join query and get the result since the SQL join query is faster when compared with the Join transformations.

If the table is in the same database, writing a simple Join query is the fastest. Also when joining these 2 tables be sure that the keys that you're going to use have proper index. 1. using merge join, you only do this if the sources are separated (different Server). 2. Aside from merge join, you can also use Lookup, on deciding which should be lookup and source, it depends which one is the leading table, hence the term lookup. Randy Aldrich Paulo MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog BizTalk Message Archiving - SQL and File Automating/Silent Installation of BizTalk Deployment Framework using Powershell > Sending IDOCs using SSIS

1) Merge Join transformation requires the data to be sorted. Sort transformations are pretty expensive as they need the whole dataset to be in memory before sorting can be applied. Also merge join transformation are partially blocking transformations which will slow down the package overall cause they move data in buffers. 2) Write a join in the source query executes the query on the server before fetching the data into SSIS. So your processing will not beign until the query is executed in th server and starts streaming to SSIS 3) Lookups are better alternatives to Merge Join (most situations) but they do cache the lookup keys which might cause a problem. So your best bet is to write the query in the source and have a lesser performance hit on the SSIS process.http://btsbee.wordpress.com/