Ask the Expert

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?

    Requires Free Membership to View

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...

This was first published in February 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: