Copying a Table from Oracle 10G XE to SQL 2000 Server using DTS - Creating a New DTS Package
(Page 2 of 4 )
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.
Next: Adding a Transform Data Task >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 8 
