.Step 6: Configuring the SQL Server Destination Component

1. Right-clic k the Sql Server Destination component and from the drop-down click on Edit….
This opens up the SQL Destination Editor displaying the connection manager configured earlier.

2. Click on the New… button. "Use a table or view:".
This pops-up the Create Table window shown superposed on the SQL Destination Editor.

3. In the Created Table window, replace the SQL Server Destination by some name of your own. Here, for this exercise, it is called Oracle Table. Click on the OK button on the CreateTable window after changing SQL Server
Destination to OracleTable.

4. Now that the connection manager and the table are chosen, click on the Mappings list item on the left.
This opens the Mappings between the View on the Oracle 10G XE and the Available Destination Columns.

5. Accept this default mapping and click on the button OK on the above editor to complete the SQL Server Destination configuration.

Step 7: Building and Executing the Package

1. Build and execute the package.

The program runs and after a while the source and destination components turn green indicating that it was a successful run. Open the OracleTable table in the SQL Server to verify that data has been transferred as shown below.

NOTE: It is important to note that the correct provider for the SQL Server destination is the Microsoft OLE DB provider for SQL Server.

Summary

In this chapter the steps involved in transferring the data in a Table in the Oracle 10G XE server to a table in the SQL Server 2005 , were described. Installing and reviewing the objects in the Oracle 10G XE's hr database was also looked at.

First Page: SQL Server Integration Services