SQL Server 2008 to Oracle 11g SSIS data loading Performance…and Attunity!

Using the Native 32bit Drivers for Oracle within BIDS, i found that running a data load from SQL Server to Oracle was proving to be very slow – Transfer rate of 2350 rows per minute.

image

This was unacceptable as i had to data load a lot of data so as an experiment i tested OPENQUERY via linked server as from past experience this is generally quite quick at returning data.

I created a linked server within SQL Server using the OraOLEDB.Oracle provider and then tested a data load via a INSERT OPENQUERY:

--INSERT:
insert openquery([MYORACLESERVER.MY_ORACLE_SCHEMA], 
'SELECT 
my_id, 
my_effectivefrom, 
my_effectiveto, my_ref, 
my_imageid, 
my_imagenotexactmatch 
FROM MY_TABLE')
SELECT 
my_id, my_effectivefrom, my_effectiveto, my_ref, my_imageid, my_imagenotexactmatch 
FROM MY_TABLE

This did the data load within 6 minutes giving around a 5x increase in performance.

As a result i have now replaced my DATA FLOW tasks within SSIS with Execute SQL tasks:

image

This was good enough for what i was doing but i also came across the Attunity Connector for Oracle, so have been doing some testing with this connector.

Available here for SQL Server 2008 : http://www.microsoft.com/en-us/download/details.aspx?id=29284

Guide here: http://msdn.microsoft.com/en-us/library/ee470675(v=sql.100).aspx

More updates on this as I complete testing…

UPDATED – 2012-09-04 with Attunity Testing

The Attunity connector for Oracle has surpassed all my expectations by loaded the 87000 rows in jus over 2 seconds.

image

image

This shows that with the right drivers and tools and with some understanding of the underlying concepts, big gains can be made by tuning at the software level. My original table was likely to take 37 minutes and my proposed solution now takes 2.05 seconds! Approximately 1082x better

Advertisements

Package Access Runtime Application with ODBC

I build a little application in access 2007 which connected to a SQL Server backend via a SQL authenticated Login.

I packaged the solution up as an installer, but didn’t want to manually go round pushing out ODBC connection separate to the install process. I wanted to be able to install the ODBC as part of the main installer, and then be able to remove the ODBC connection when the app was uninstalled.

The error in question:

image

The Access package solution wizard is useful for inserting registry keys into a machine as part of an install which would resolve the above error.

To enter an ODBC connection in the registry, i added the following keys (Enterprise Director is the name of my ODBC connection)

Root: Current User
Key: Software\ODBC\ODBC.INI\Enterprise Director
Name: Driver
Value: C:\Windows\system32\SQLSRV32.dll

Root: Current User
Key: Software\ODBC\ODBC.INI\Enterprise Director
Name: Server
Value: ServerName

Root: Current User
Key: Software\ODBC\ODBC.INI\Enterprise Director
Name: Database
Value: DatabaseName

LastUser is the SQL authenticated login to access the database
Root: Current User
Key: Software\ODBC\ODBC.INI\Enterprise Director
Name: LastUser
Value: CMEntDir

image

The overall effect of having these keys is that the ODBC is installed automatically without any additional action.

When opening the App, i know get the following prompt:

image

and the registry key is installed:

image

image