Tuesday, July 15, 2008

Mark Malakanov poorly maintained blog: July 2007

One workaround for the notoriously slow insert times from SSIS into Oracle.

SSIS 2005. How to avoid Autocommit when load into OLE DB destination.

An example with using of Oracle OLE DB driver on a destination side.

I have heard from my friend that he is experiencing very slow load into Oracle database. He used SSIS 2005. Which is funny, because Oracle Warehouse Builder is available. Anyway.
The cause of slowness of SSIS, when it loads into non-Microsoft OLE DB target, appeared to be the commit after every insert. Since the ‘Autocommit’ is set to be ‘ON’ by OLE DB standard, a driver propagates this mode to the database. The only way to turn it off, is to switch OLE DB datasource into a Transactional mode by issuing a BeginTransaction() call.

Mark Malakanov poorly maintained blog: July 2007

No comments: