Transferring 2 million records from SQL server to Oracle using simple OLE DB Source and Destination in SSIS. The package hangs in yellow showing 7000 rows passed to destiantion, nothin happens for hours, next day I came in and saw 70000 records processed and still in yellow. Today the same thing is happening. It works(2 million records processed in 40 secs) when I change the destination to SQL Server. I am guessing its something to do with the Oracle destination. I am really new to oracle environment. So what can I do? is it something I have change in SSIS pakcage or the destination itself. If you guys need more info about this. please ask. Thanks

Which version of SSIS? In 2005 the driver is very poor and it likes to do a commit after each row is inserted.

Well. try to set the Maximum Insert Commit Size property to 2147483647 in OLE DB Destination. Hard to say why w/o diagnostics. If the above will not help. Then you will need to involve both sides, the Oracle and SQL Server. May need to use SQL Profiler to see at what step it hung. Also please skim through http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/2cb0bec7-0fe2-4b8d-b59e-9534388c72e7 Arthur My Blog

Am using SSIS 2008. I set the Max commit insert size. It wont help. And as far as I know, not possible to run the profiler for Integrations services.

In order to find out more about where the bottleneck is, I suggest you "decompose" your data flow. First, remove the destination, and replace it with a Row Count. If the flow executes significantly faster, then you know the bottleneck was due to the destination. If not, then remove all transforms after the source, routing the source directly into a Row Count. If the flow executes faster than the previous test, then you know the bottleneck was the transformations. If not, then the bottleneck is the source. Please let us know where the bottleneck is, then we can provide better help. Talk to me now on

Transferring 2 million records from SQL server to Oracle using simple OLE DB Source and Destination in SSIS. The package hangs in yellow showing 7000 rows passed to destiantion, nothin happens for hours, next day I came in and saw 70000 records processed and still in yellow. Today the same thing is happening. It works(2 million records processed in 40 secs) when I change the destination to SQL Server. I am guessing its something to do with the Oracle destination. I am really new to oracle environment. So what can I do? is it something I have change in SSIS pakcage or the destination itself. If you guys need more info about this. please ask. Thanks If only by simple replacement of the destination to SQL Server everything works properly, then the issue is clearly with the Oracle's OLEDB destination. The SQL Server OLEDB destination is very fast because it supports the FASTLOAD API. Most OLEDB providers doesn't support this API and the driver issues INSERT INTO ... statement for each row of data. This may work for a couple thousand records, but for more records it will be useless. If you can use third-party components, check the commercial CozyRoc Oracle Destination component. It is 30x times faster compared to the standard OLEDB destination. One million records insert in less than 1 minute.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Double check you don't have an uncommitted transaction open in Oracle in another tool such as SQL Developer or Toad. Being so used to SQL Server's transaction behaviour I am forever forgetting to commit Oracle transactions, and this can lead to other processes being blocked. As others have mentioned inserting large amounts of data into Oracle via OLE-DB connection is well known for being slow. I have had good results using the Microsoft / Attunity connectors, although they are available for 2008 only.http://www.sqlis.com | http://www.konesans.com

@Todd, It was a simple OLE DB Source and Destination component, no transformations or anything. So the bottle neck had to be Oracle environment. @CozyRoc and @Darren, thank you guys, the Attunity connectors is doing the job. Right now running the package and 300000 rows in less than a minute. Is there any more tweeking I can do in the connectors to speed up the process? I see some properties like Batch size, TransferBufferSize. I really appreciate the ideas.

There's nothing "guaranteed" to make your process occur faster, but you should experiment with a few things - one at a time, of course. The properties on the OLE DB Destination - specifically the "fast load" option, as well as batch sizes and commit sizes can be tweaked. You may want to look into splitting your single data flow into multiple "vertically partitioned" data flows that execute in parallel. (Data flow 1 transfers "all customers from A-M", data flow 2 transfers "all customers from N-Z"...) You should look at performance counters on each machine. You've basically got four resources on each machine that you're looking to "balance" so that you're using each resource as close to it's limit as possible: CPU, RAM, Network I/O, Disk I/O. If you have any one of those resources hitting it's limit, then you can try to rearchitect your solution to use that resource "less", or "better". If you can make your system's usage of that resource more efficient, then you can (theoretically) make the whole system work faster. Talk to me now on

Hey Todd, Set the BatchSize to 100000, increased the TransferBufferSize and Prefetchcount. It works lot better. I appreciate your time. Thanks.