Copying a Table from Oracle 10G XE to SQL 2000 Server using DTS - Adding a Transform Data Task
(Page 3 of 4 )
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:
CREATE TABLE [EMPLOYEES] (
[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.
Next: Executing the Package >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 8 
