i have a source which has about 50mil records. i have to insert into destination any records that are in the source and not in the destination. Right now i have about 20mil in the destination. i cant use lookup, because it will thow up error once its cache is full and if i switch to partial cache its too slow. So i am using a left join in the source on 5 columns and i am inserting the new records. i am getting all the new records fairly quick from the source but they are not getting inserted into destination. I see a bottle neck at the destination. if i use the same left join and try to insert into the same table in SSMS it inserting into the destination albeit slow i.e 10,000 records takes 5 min, but when i am doing it in the ssis it's taking for ever. i tried to run it for 1.5 hrs it did not even insert 1000 records. i am unable to figure out what the problem is. there are no key violations or any thing of that sort in the destination because i was able to insert like 10 records( i selected the top 10 of the sql join).. Is the bottle neck because i am using the destination table in the left join in the source and trying to insert the result set in the destination or is it because of other reasons. please help

See if this post helps: SQLIS | Get all from Table A that isn't in Table B http://www.sqlis.com/sqlis/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspxArthur My Blog

If the data is on the same server, then I would use a SQL query, perhaps using the executesql task or just creating a SQL Agent job. Using a data flow is considerably slower than a query because all of the data is brought out of SQL Server into a new process and perhaps across the network to another machine and then pushed back to the same SQL Server.Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008

thanks for the reply but it did not help much. The first method in the article uses sort which is asynchronous transformation which will hit the performance which i do not prefer and the second method uses lookup which i can't use because it throws up error after the cache is full and i cant switch to partial cache because its slow. Thanks anyway

Hi Reddy335, You can try following thing and let me know if this is working for you or not. In your package in OLE DB Destination task, Use Data Access Mode as 'Table or View Fast Load' and you can even specify the rwocount in 'Rows per Batch' This would surely minimize the package execution time. Hope this help ! Thanks, Vaibhav Please mark it as correct answer if this helps you to solve your problem.

The problem with your left join in SSIS is that you are reading from the table while you are at the same time inserting new records in the same table. Specify WITH(NOLOCK) on the destination table in the LEFT JOIN statement. Make sure you use fast load in the OLE DB Destination. If you do not want to use the query hint, try the MERGE JOIN option from SQLIS, but instead of using the SORT component, use an ORDER BY in your SQL SELECT statement and mark the result set as sorted in the dataflow.MCTS, MCITP - Please mark posts as answered where appropriate.

I am already using that data access mode. Thanks for your reply

I dont thinkk the situation can be beacause of left joins control used, as the things within SSIS works well when we use the Lookup Control it essenatilaly works on the same way i.e. the selection happesn from source and insertion also happens on the same time. a) Try using a SQL call, SP to insert and upate. b) TRY SCD modicifed controls somehting which uses, HAshing to run the SCD, this is helpful for lasrge records http://dimensionmergescd.codeplex.com/, provides one such custimised SCD cotrol. Abhinav