Posted by: Charles Maitland | Thursday 2 February, 2006

Getting Data from Oracle using SSIS

As part of the data warehouse and Reporting Services project I am working on I have to extract data from a finance system which is running on Oracle on a Sun Solaris box.
One of the consultants from the company that supplies the software came and spent a day writing the queries that would extract the data. In fairness to him he did emphasize that the queries were not in any way optimized but the volume of the data and time constraints prevented us doing much more with them.
This week I came to plug the queries into the Integration Services connector to start building the import scripts.
No way could they run. They were impossibly slow. The query to extract the Cost Centre and Analysis codes took 7 hours to run. (Over night).
I tried using different connectors and both Oracle and Microsoft drivers.
I tweaked settings and fiddled with the query. Nothing helped.
With the help of the client’s Sun and network gurus we tracked down the problem to Oracle’s processing of the query and the resources on the Sun box.
I was then faced with the option of getting in an Oracle expert to tweak the query, and we suspect, tune the database or see what SQL server could do.
The result is that I now suck into SQL Server all the tables that the queries touch on into a staging database. I use the same table and field name as the source but only bring in the fields used by the query.
I then ran the same Cost Centre query and it completed in 15 Seconds!
I can now use the queries I have and populate the data warehouse from this staging database.
I know that there are hundreds of factors that are having a higher impact on this issue than SQL Server – V – Oracle, BUT
My conclusion is that it has reinforced my belief that if you are needing a very large database system AND have the budget to employee and keep a small army of highly qualified DBAs then Oracle can fulfill your needs. If, however, you want the same performance or better on a system that makes managing and actually USING the database easy then SQL is for you.
Oh and don’t get me started on the tools and utilities for accessing and using Oracle my blood is still simmering!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: