All, I would like to insert changed and new records only. My question here is I have a flat files as a source and sql server 2008 as my destination. How do I do a left join to a flat file data from my existing sql server data while using look up transformation? Do I have to dump flat file data to any sql table? is that mandatory? I appreciate if you can share any case you already worked.. Thanks

Hello, Use flat file source and make connection to your flat file. after make the connection, get the lookup and write query to get data from your sql server table. Map the columns,On Error instead of redirecting rows, You need to Select "Ignore Failure"... Write to your destination. This will wrok as left join. helpful link http://sqlblog.com/blogs/andy_leonard/archive/2010/02/16/ssis-snack-lookup-transformations-101.aspx Thanks Aamir

As Aamir said, your source is flat file, and there is no source limit for lookup transform, just your lookup table should be in oledb connection ( which in your case your sql server table is ), and note that you can set the "specify how to handle rows with no matching entries" to "redirect rows to no match output".http://www.rad.pasfu.com