Copying a Table from Oracle 10G XE to SQL 2000 Server using DTS

Database users sometimes need to transfer data from one vendor type to another. Sometimes data stored in a large scale database such as Oracle or MS SQL Server may be transferred to smaller desktop database applications such as MS Access, MS Visual FoxPro, etc. This tutorial is an exercise in such a transfer from Oracle 10G XE to SQL 2000 server using Data Transformation Services. I urge readers to review the articles on DTS in particular and data migration in general on the ASP Free site to get the most out of this tutorial.

Tutorial Requirements

The tutorial assumes that you have a working Oracle 10G XE program, a great product. If you do not have this product, you can install it for free. The details for getting the program and installing are discussed at this link. You must also have the MS SQL 2000 Server working on the same machine, or a connected machine. For this tutorial both programs are running on a Window XP Professional machine.

By going to Start–>All Programs–>Oracle 10G Express Edition–>Go to Database Homepage you can access the login page for the database. Assuming you have added the hr user, you can log in with the pair hr/hr and get access to the HR database. After you are authenticated you may click on the Object Browser icon to reveal the objects. From the drop-down Tables, you will be able to display all the table names. You may choose the Employee table that we will use the DTS to transfer to the SQL 2000 server. The next picture shows the design details of this table.

The following is a sample of the data from this table.

{mospagebreak title=Creating a New DTS Package}

We are creating a DTS package that will be stored in the MSDE Server XPHTEK NetSDK. From the Data Transformation Services node of this server you can access the New Package submenu by a right click on this node, as shown in the next picture.

Adding Connections

This opens up the DTS design editor as shown in this picture. The menu items give access to connection, task, and workflow items. You may also drag and drop objects from the connection collection shown on the left with various icons. You may also add tasks by dragging and dropping on the editor pane. The Microsoft ODBC Driver for Oracle is shown in the collection of connections. Since we are importing from Oracle 10G XE we drag and drop this connection to the design pane.

This opens up the Connection Properties window as shown. The Data source: is the driver that was added to the pane. You need credentials to access the Oracle 10G XE. The server is xe, and the credentials are hr/hr.

If you need to customize the settings you may do so by clicking the Advanced… button. Here only the defaults are used.

Click on OK to close the above and click once again to close the Connection Properties window. Now drag and drop the SQL server from the connection collection which adds the Microsoft OLE DB Provider for SQL Server. This opens the Connection Properties window once again as shown. The TestRun database on the local server will receive the copied database as a result of executing this package. You cannot access it unless the authentication information is properly set. In this case the SQL Server Authentication is used with an existing UserName and Password. Click OK when all this is entered.

{mospagebreak title=Adding a Transform Data Task}

Now you see the two connections in the design pane. Now we will add a Transform Data Task so that the information flows from the source to the destination.

Click on the Transform Data Task icon, bring the cursor over to the source as shown and click on the Microsoft ODBC Driver for Oracle connection.

Now move the cursor over to the destination connection as shown.

An arrow pointing from source to destination will be displayed, connecting the two connections. On the arrow, right click, and from the drop-down click on the menu item Properties.

The Transform Data Task Properties window pops up with the tab showing the source. When you click on the arrow alongside the radio button Table/View, you can see all the tables in the Oracle 10G XE database. Click on the “HR”.”Employees” table. If you need to, you may also create a SQL query.

By clicking on the Preview… button you will be able to see the contents of this table.

Click OK to the View Data window and click OK once again to the Source. Now click on the Destination tab of the Transform Data Task Properties window to open the page shown in the next picture.

It comes with a table on the TestRun database. Since a new table has to be created, click on the Create… button. This opens up the Create Destination Table window with a SQL script to create a new table. It has taken the information from the source as to the structure. If you need to make changes you may do so. Here the default is taken. Even the table name was not changed.

The script that is executed is shown here:

[EMPLOYEE_ID] numeric (6,0) NOT NULL, 
[FIRST_NAME] varchar (20) NULL, 
[LAST_NAME] varchar (25) NOT NULL, 
[EMAIL] varchar (25) NOT NULL, 
[PHONE_NUMBER] varchar (20) NULL, 
[HIRE_DATE] datetime NOT NULL, 
[JOB_ID] varchar (10) NOT NULL, 
[SALARY] numeric (8,2) NULL, 
[COMMISSION_PCT] numeric (2,2) NULL, 
[MANAGER_ID] numeric (6,0) NULL, 
[DEPARTMENT_ID] numeric (4,0) NULL )

When you click on the OK button on the previous window, the structure of the table shows up in the destination tab of the Transform Data Task. When you click OK to this you will go to the Transformations window. You may go back and forth on the tabs until you are satisfied that the task is properly configured.

The Transformations page shows the way the data flows from source to destination, column by column. These connections are completely configurable. Please refer to my earlier articles on DTS which shows how they may be altered. Here the defaults are accepted. Again the defaults of the Lookup and Options tabs are accepted. Now click on the OK button.

{mospagebreak title=Executing the Package}

With this, the configuration of the task is completed. From the Package menu item the package can be executed by clicking on the drop-down menu item Execute.  This brings up the Execute Package window, which shows the progress of the package execution as shown. When it is completed, the Package Execution Results window pops up as shown.

You may click on the OK button, and on the Done button on the above windows to close them. You may save the package for future use, or for reference on the server as shown. A name has to be given to the package.

This shows the Employees table created on the TestRun database on the local server.

The table properties can be accessed from the database’s table node as shown.

This next picture shows the original table from Oracle 10G XE as well as the table copied to the SQL 2000 Server.


Some of the package properties can be edited by accessing the submenu item Disconnected Edit… from the drop-down from the main menu item Package.


SQL 2000 server’s DTS program works without a hitch copying a table from Oracle 10G XE to SQL 2000 Server. The Oracle connection uses a default ODBC connection, whereas in SQL 2005 server, the Integration Services uses the Native Clients and OLE DB providers. Configuring a similar task in Visual Studio’s IDE requires attention to a lot more details and is not as intuitive.

One thought on “Copying a Table from Oracle 10G XE to SQL 2000 Server using DTS

  1. SSIS is better than DTS, but sometimes DTS is simpler. For example there is no copy table task in SSIS. In any case, in real life you cannot expect to have the latest and the greatest. There will always be someone using DTS, SQL 2000, SQL 6.5 etc.

    I look forward to your comments. Make sure you review the dozen other articles on DTS on the ASP free forum.

    Bye for now,


[gp-comments width="770" linklove="off" ]