.

.

.

.Step 3: Configuring the OLE DB Source

1. Right- click the OLE DB Source component on the canvas and bring up the OLE DB Source Editor, as shown in the next screenshot.

The OLE DB Connection Manager is the recently created connection manager xe.hr. As we are accessing a Table from the database, the default for the Data access mode: is acceptable as it is.

2. Click on the drop-down for the Name of the table or view:
This field reveals all the accessible objects on the hr database on the Oracle 10G XE Server.

3. Choose the view, "HR"."EMPLOYEES", we saw earlier in the object browser of this database.

4. Click on the Preview… button to see the Table that is being used from the Oracle 10G XE through this connection.

This brings up a Microsoft Visual Studio warning page as shown in the following screenshot. Ignoring this warning and continuing by clicking on the OK button on the window with the warning will show all the columns of the EMPLOYEES in the Preview Query Results window.

5. Click the OK button.

Step 4: Adding a SQL Server Destination and Configuring its Connection Manager

1. Drag and drop an SQL Server Destination component from the Data Flow Destinations group, from the Toolbox onto the Data Flow page of the Canvas.

2. Following a procedure similar to the OLE DB Connection Manager, configure a Connection Manager (LocalHost.MyNorthwind) for the SQL Server Destination.

The following screenshot shows the final screen of the Connection Manager window, after the connectivity test using the Test Connection button. The Provider chosen is the Microsoft OLE DB Provider for SQL Server. You may notice that the (.) format is used for designating a default SQL Server 2005 installation. This server uses SQL Server Authentication. After choosing the authentication, you will be able to Select or enter database name from a drop-down list. In case your server uses Windows authentication, you don't need to provide the username and password.

Step 5: Establishing a Path from the OLE DB Source to the SQL Server Destination

This has been described in most of the tutorials , so wont be repeated here.

1. Establish a path from the OLE DB Source to the SQL Server Destination. The Metadata of the path connecting the source and the destination will appear as shown in the Data Flow Path Editor window below.