CODEWITHSUNDEEP
pythonsql-serveroracle-databasessisetl
Srikar mogaliraju
Srikar mogaliraju

Reputation: 215

Migrating multiple tables data from SQL Server to Oracle

I have a scenario to migrate SQL Server tables(30- 40 tables) to Oracle. I Cannot depend on SSIS as the no of tables to be migrated to Oracle will change regularly and I cannot always create or update a DFT when ever there is a change in schema. Is there any other way where the movement of data can be handled dynamically and can work effectively ? Like using Python or any other Programming languages ?

Upvotes: 1

Views: 1507

Answers (2)

Srikar mogaliraju
Srikar mogaliraju

Reputation: 215

Here is the approach I have decided to go considering the time constraint( using C# is taking more time).For 8 GB table it is taking 11 minutes to move the data SQL to Oracle.

Steps:

  1. Dump the SQL tables data into flat files.(Used BIML for automating the DFT creation)
  2. Transfer these flat files to the Destination server.
  3. Using SQL*Loader to load data from flat files to Oracle.

Upvotes: 0

Hadi
Hadi

Reputation: 37348

C# approach - SchemaMapper library

Since you are open to a solution using a programming language, i think you can benefit from SchemaMapper class library which is an open-source project published on GitHub. A full description can be found in the Readme file on the link above.

Important Note: Yesterday i added the support of reading data from databases (SQL Server , Oracle ...) and the ability to export data to Oracle.

In this answer i will provide information on importing SQL Server tables, create the appropriate SchemaMapper class for each one (since they have different schema and you need to import them to different schemas), and how to export data to Oracle.

//First of all list the tables names need to import
string[] TableNameFilter = new[] { "Table1", "Table2" };
//Create an instance of the oracle import class
SchemaMapper.Exporters.OracleExport expOracle = new SchemaMapper.Exporters.OracleExport(oracleconnectionstring);

//Create an SQL Server import class
using (SchemaMapper.Converters.SqlServerCeImport ssImport = new SchemaMapper.Converters.SqlServerCeImport(sqlconnectionstring))
{

    //Retrieve tables names
    ssImport.getSchemaTable();

    //loop over tables matching the filter
    foreach(DataRow drRowSchema in ssImport.SchemaTable.AsEnumerable().Where(x => 
                                                                      TableNameFilter.Contains(x["TABLE_NAME"].ToString())).ToList())
    {

        string SQLTableName = drRowSchema["TABLE_NAME"].ToString();
        string SQLTableSchema = drRowSchema["TABLE_SCHEMA"].ToString();

        DataTable dtSQL = ssImport.GetDataTable(SQLTableSchema, SQLTableName);

        //Create a schema mapping class
        using (SchemaMapper.SchemaMapping.SchemaMapper sm = new SchemaMapper.SchemaMapping.SchemaMapper(SQLTableSchema, SQLTableName))
        {

            foreach (DataColumn dc in dtSQL.Columns)
            {

                SchemaMapper_Column smCol = new SchemaMapper_Column();
                smCol.Name = dc.ColumnName;


                smCol.Name = dc.ColumnName;
                smCol.DataType = smCol.GetCorrespondingDataType(dc.DataType.ToString(), dc.MaxLength);

                sm.Columns.Add(smCol);

            }

            //create destination table in oracle
            expOracle.CreateDestinationTable(sm);

            //Insert data
            expOracle.InsertUsingOracleBulk(sm, dtSQL);

            //there are other methods such as :
            //expOracle.InsertIntoDb(sm, dtSQL);
            //expOracle.InsertIntoDbWithParameters(sm, dtSQL);

        }

    }




}

Note: this is an open-source project: it is not fully tested and not all data types are supported, if you encountered some errors feel free to give a feedback, or add an Issue in GitHub

Other approach - SQL Server Import and Export Wizard

If you can do this without scheduling a Job, then you can use the Import and Export Wizard which allows you to import multiple tables into Oracle without the need to build the packages manually. It will create packages, destination tables, map columns and import data.

Upvotes: 1

Related Questions