July 7, 2016 at 8:01 pm
Evening All,
I am just wondering if anyone has the experience of utilizing SSIS as the ETL tool in an Oracle Data Warehouse environment.
Most of the source systems are on SQL Servers but the Data Warehouse is on an Oracle platform. I was wondering if any one has done a similar implementation and has any advice or can provide any pros and cons to using SSIS.
Thank you
July 8, 2016 at 5:17 am
I used SSIS against an Oracle DW a few years ago.
Generally it wasn't too bad but the oracle drivers for SSIS are not as good as the ones fro SQL server so loading data into a destination can be a bit slower.
Also, calling stored procedures (or whatever Oracle calls them) was slightly different - I cannot remember exactly what but it wasn't too painful.
Jez
July 8, 2016 at 12:03 pm
Thank you - it's good to know others have tried and succeeded. I am hoping the Attunity Oracle components work well
July 8, 2016 at 2:21 pm
I'm currently using SSIS to load data from various sources into an Oracle database.
You definitely want to use the Attunity providers. They are orders of magnitude faster than the default OLE provider when talking to Oracle.
Be careful using the 'fast load' option with the Attunity drivers however. This will bypass all checks and constraints on the Oracle table, which could lead to a corrupted table if your data is not as clean as you think it is. I don't use the 'fast load' option, and haven't noticed much of a performance difference.
The Attunity driver is also does not do any implicit data conversions, and sees Oracle data types differently in some cases, so you'll probably end up using Derived Column transforms quite a bit to cast columns to the data type that the Attunity driver 'sees'. For instance, to load a date value into an Oracle 'Date' column, you'll need to cast it as a DT_DBTIMESTAMP in SSIS.
Good luck!
July 9, 2016 at 1:58 am
Thank you very much for the useful tips. Great information.
Thank you very much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply