have you tried increasing the buffer size of the data flow task by default it is 10 MB and 10000 rows increase it to 100Mb and it should increase the performance-------------------------------------------------------- Surender Singh Bhadauria

I really don't get what you mean by "pull and insert data in batches". On the other hand, we have no access to Oracle database and the query for extracting data is the only point of contact with Oracle. It has been refined by couple of DBA's. Something is somewhere wrong..but I cannot figure out where! insertion of 200K to single table should not take that long. It is either the network bottleneck or DB server overloading or provider. or.... I don't know what. Only thing I know It has to be faster. Thanks for input.Mathew.

Following article may be of your interest: http://blogs.msdn.com/b/sqlperf/archive/2009/08/14/using-ssis-to-get-data-out-of-oracle-a-big-surprise.aspxShailly - If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

Hi, I have an issue to fix. here is the scenario: 1. Reading from oracle with ADO.NET Source. 2. It is daily job to flat staging table. each day 200K records. 3. There is no index whatsoever on the staging table. REading from oracle itself take about 20 minutes and the query apparently is the most optimized possible. ( tables the query are coming from has 40 Mil record for each partition). My issue is inserting to sql takes around 30 minutes. I have tried ADO.NET Destination and SQL Server Destination but both are not fast enough. I am now thinking of using OLEDB destination but the package is already in production and I need to be at least 50% confident to pull it down and modify it. As for POC perspective with amount of data we have in development environment oledb and ado.net perform the same. Appreciate your inputs. Thanks.

I think the type of connection provider would have least effect in the overall performance. It would be a better idea if you try to modify the way you are reading data from source. One way which I think is to pull and insert data in batches.Shailly - If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".