Problem solve Get help with specific problems with your technologies, process and projects.

Importing an Oracle text file into SQL Server 2000, part 1

I'm quite newbie in exploring the database technologies whether Oracle or MS SQL Server. I want to export Oracle 9i database to text file, then import it to MS SQL Server 2000. Is there any solution that will work for me? Do you have any suggestions?

Migrating data from Oracle to SQL Server has been a typical responsibility for DBAs and developers in organizations with a heterogeneous database environment. The process to migrate data from one database platform to another is called extraction, transformation and loading (ETL). The primary tool in SQL Server for ETL processes is Data Transformation Services (DTS). DTS is a free tool that has shipped with SQL Server since version 7.0. With SQL Server 2000, DTS offered a richer feature set and improved reliability (DTS Basics). With DTS the goal is to build DTS Packages with the DTS Designer, which is part of Enterprise Manager, that encapsulates all of the ETL logic not only from a graphical perspective, but also programmatically via a comprehensive object model (DTS Object Model Diagram).

Under your circumstances Microsoft has built the connectivity in an inherent manner between Oracle and SQL Server via a native OLE-DB driver. This means it is not necessary to have an intermediary step to export and import from a text file. As such, data can be directly migrated from Oracle to SQL Server via the DTS objects. In order to build a DTS Package to achieve this goal, three primary DTS objects are needed:

  1. Connections –- Source and destination objects that incorporate all of the connectivity parameters
    • Default connections for SQL Server 2000 DTS
      • SQL Server
      • Microsoft Access 2000
      • Oracle
      • Paradox
      • DB2
      • DBase 5
      • Other ODBC-compliant database
      • Microsoft Excel 2000 worksheet
      • HTML file
      • Text file
    • For additional information visit DTS Connections.
  2. Tasks -– A unit of work in the DTS Package where business logic is encapsulated
    • Tasks include:
      • Data Driven Query Task
      • Transform Data Task
      • ParallelDataPump Task Object
      • Copy SQL Server Objects Task
      • Execute SQL Task
      • Bulk Insert Task
      • Transfer Database Objects Tasks
      • ActiveX Script Task
      • Dynamic Properties Task
      • Execute Package Task
      • File Transfer Protocol Task
      • Execute Process Task
      • Send Mail Task
      • DTS Custom Task
    • For additional information visit DTS Tasks.
  3. Workflows –- Logic between various tasks in the DTS Package outlining a subsequent action
    • Logical Operators
      • On Success (Green Line)
      • On Failure (Red Line)
      • On Completion (Blue Line)
    • For additional information visit DTS Package Workflow.

A simple DTS Package to truncate (delete) all of the SQL Server data, migrate data from Oracle to SQL and notify the DBA team on success or failure would look like the following in the DTS Designer:

Figure 1 – Sample DTS Package to migrate data

This response is continued...

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.