need some best practice tutorials for Sql server to oracle ETL using SSIS

Question

need some best practice tutorials for Sql server to oracle ETL using SSIS

mahdi-the-man

SSChasing Mays

Points: 645
More actions
April 4, 2017 at 1:12 pm

#342197

I'm having trouble in low performance and memory usage and faults in using SSIS to transfer data from some Sql Servers to the Oracle Database as a data Warehouse.
by now,I just delete whole tables data and insert them again every night. I cant using Merge or Fast Load in my scenario.
and now looking for some best practices and tutorials in this topic but can't find anything. there are only oracle to sql server and sql server to sql server.
any suggestions appreciated
thanx

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Personally, I wouldn't use SSIS. That, notwithstanding, try the following Google search. There seem be some tutorials (MSSQLTIPS is usually good) and YouTubes on the subject.
https://www.google.com/?gws_rd=ssl#q=ssis+transfer+data+from+sql+server+to+oracle&*&spf=381

In that search, you'll also find the following link titled "Optimized Bulk Loading of Data into Oracle", which at least seems to be something of what you're looking for. Look for the Word Document link near the end of the very short article.
https://msdn.microsoft.com/en-us/library/hh923024.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16155 More actions · Answer 2

Regarding the links that Jeff posted be aware of some of the issues with it.
Firstly the Attunity driver is only for Enterprise Edition.

As for the driver itself.
It has 2 modes
Fast load
Array load

Each one of them has its own issues.
For example Fast Load will replace any string with spaces on it with nulls. E.g. will trim all spaces, and then as Oracle does not allow a empty string it replaces with a null. Array mode will keep the spaces as they are.

Although normally the Fast Load is faster than the array load that is not always the case - trial and error to see which one behaves better.

Also, and contrary to what the documentation says, setting up the buffer size on Fast Load does not work as described in some of the versions (haven't tried the last one).

I would consider the following options to load onto Oracle
1 - sqlloader - only if your source are flat files, or if performance testing done results on extract to file + sqlloader being faster than any of the other options - note that sqlloader can be highly customizable and tuned.
Also the only option if as part of the load you need to get sequence numbers without an intermediary table

2 - c# with OracleBulkCopy - nearly as fast as sqlloader but not customizable

3 - SSIS with the Attunity driver or one of the other drivers on the market (CozyRoc for example)
Attunity driver will be 1.5/2 times slower than sqlloader

both option 2 and 3 are picky with datatypes, in particular SSIS. Conversion must be done in almost all cases dealing with numerics and dates.

Strings also need to be properly manipulated due to codeset differences.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

As personal experience, I would suggest that you avoid loading data into Oracle using SSIS. Even with specialized drivers as Attunity, the load will be a lot slower than using flat files.
The process is to use bcp to create the flat file, copy it to a place available to the Oracle server and load it using sqlloader.
You'll need a format file for sqlloader and you might want one for bcp.
Depending on the warehouse design, you could fully delete and load dimensions, but fact table would be better if you only load one day (or a couple) at a time. This way, your loads won't become incrementally larger every day.
You should use staging tables to make any complex process in Oracle (anything more complex than a simple delete and insert).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mahdi-the-man SSChasing Mays Points: 645 More actions · Answer 4

thanks everyone .. i changed the adapter to Oracle Attunity, and everything's going right!

I have to use SSIS in my scenario, but I don't know anything about Attunity driver..thanx for ur points here lead to solve my problem.