Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Tuesday, June 19, 2012

SSIS Transactions Including Oracle Data Sources

Microsoft SQL Server Integration Services has the out-of-the box capability to handle transactions. I won't get into the details of how to use it as this post is specifically geared towards including an Oracle data source/Connection Manager in a transaction. For an overview of transactions in SSIS as well as enabling MSDTC to support this capability, please refer to the links below.

When using transactions with only SQL Server Connection Managers the typical gotcha for the uninitiated is the MSDTC requirement mentioned above. When using an Oracle-based Connection Manager there is an additional dependency. This dependency is the Oracle Services For Microsoft Transaction Server component and is available in the Oracle client installer. Below is a screen shot of the installation screen with this component highlighted. 


Once MSDTC and the Oracle Services For Microsoft Transaction Server are installed and enabled, cross-database platform transactions are a breeze.

Wednesday, June 16, 2010

SSIS Extracting Reading from Oracle 10g

Here are a few hints if your extracting data from an Oracle database using SSIS.

1) Use Oracle 11g client. The 10g client has bugs around the naming of the Program Files directory on 64-bit machines. It doesn't like the parantheses in Program Files (x86). There are workarounds for using the 10g client but it's been my experience that it's not worth the effort.

2) Make sure to install the 32-bit client for running SSIS in the IDE (debug mode) and the 64-bit client for the 64-bit SSIS runtime.

3) Use the Attunity connector for Oracle. This component can extract data up to 100 times faster than out of the box OLE DB for Oracle provider.