Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage


  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support

  • Archives

  • Blog Stats

    • 7,604,277 Views

Archive for September 1st, 2008

Copying Data from SQL Server to Oracle

Posted by decipherinfosys on September 1, 2008

One of our clients recently had a need to quickly and efficiently copy over the data from a SQL Server database to an Oracle schema. This client is using Oracle’s solution for doing data warehousing and data mining and as a result needed data uploads from other systems one of which was on a SQL Server system. Such kind of requirements are very common nowadays. Either moving data from SQL Server to Oracle or vice-versa.

The idea was to be able to load the data quickly into an Oracle schema which has the exact same schema structure like that of SQL Server and then once the data load is done, one can run the proper transformations and then load into the staging schema. So, essentially, they were interested in having an ODS (Operational Data Store) i.e. have a schema with an exact copy of the source data (or a sub-set of the data in this case since not all the data and not all the objects were needed). The data from the ODS is then processed into another schema through transformations and then cubes are built for reporting and enhanced analytics.

The company had tried using several methods including:

a) SSIS: Direct data copy using the OLEDB providers of the respective RDBMS. But the data copy performance was very poor in this case even after tweaking several settings.

b) DataDirect: This worked out great for them but they ran into issues related to UNICODE data sets.

c) The final step that they tried was what we have listed in one of our whitepapers over here. Look at: “Copying data from SQL Server database to an Oracle Schema”. They deployed a similar scripting framework which provided them the maximum flexibility and then used SSIS as a workflow tool in order to get the data from SQL Server into Oracle in the least amount of time.

In the long run, they intend to go to the Data Direct product once their open issue pertaining to the UNICODE data sets gets resolved. In order to see how to load up UNICODE data into Oracle, here is one of our previous posts that talks about how to load up chinese data in Oracle.

Posted in Oracle, SQL Server | Leave a Comment »