This tutorial requires SQL Server 2012 and Microsoft SQL Server Integration Services Designer. A similar Integration Services package can be created with SQL Server 2008 and BIDS 2008; however, there may be some differences, mostly in using the Toolbox. In BIDS 2008, dotConnect for Oracle SSIS Data Flow components are not added to the Toolbox by default. You need to add them yourself as described in SSIS Data Flow Components Installation in BIDS 2008. The names of the Toolbox tabs are also different in BIDS 2008 and Microsoft SQL Server Integration Services Designer.

For the purpose of this tutorial, we create a Devart Oracle Source and an OLE DB Destination.

In the Control Flow tab, from the Control Flow Items tab in the Toolbox, drag Data Flow Task to the design area. The Data Flow Task is created.

Switch to the Data Flow tab. From the Other Destinations tab of the Toolbox drag the OLE DB Destination to the design area.

From the Common tab of the Toolbox drag Devart Oracle Source to the design area. (By default dotConnect for Oracle SSIS Data Flow components are added to the Common tab when installing dotConnect for Oracle.)

Connect the two data flow objects with a Data Flow path.

For this example we need two connections - one for the OLE DB Destination, the other one for the Devart Oracle Source. To create a connection to Oracle, do the following:

1. Right-click in the Connection Managers pane and select New Connection from the context menu. The Add SSIS Connection Manager dialog box is displayed:

   

2. In the Connection manager type list select DevartOracle and click Add. The General tab of the Oracle Connection Manager Editor dialog box is displayed:

   

3. Specify the connection parameters.

   1. If you connect through OCI, in the Server property type TNS alias of the server.
   2. If you use Direct mode, perform the following assignments:
      • set Direct to true
      • in the Server property type DNS name or IP address of the computer where Oracle server resides
      • in the Sid property type service name
      • In the Port property specify the port that the server listens to (usually this is 1521).

   In our tutorial we create a Direct mode connection that does not require Oracle Client.

The connection is now created.

To create a connection for the OLE DB Source we do the following:

1. Right-click in the Connection Managers pane and then click New OLE DB Connection. The Configure OLE DB Connection Manager dialog box is displayed.
2. Click New. The Connection Manager dialog box is displayed; fill in the connection settings as required, e.g. as follows:

   

   Click OK.

3. Click OK in the Configure OLE DB Connection Manager dialog box.

The connection for the OLE DB source is now created.

Note:
If you create an Integration Services package in BIDS 2008, you will need to do additional actions to open the Configure OLE DB Connection Manager dialog box instead of simply selecting New OLE DB Connection from the shortcut menu. To be more specific, you will need to right-click in the Connection Managers pane and then click New Connection instead of New OLE DB Connection, as the latter is not present in BIDS 2008. After this, the Add SSIS Connection Manager dialog box is displayed. In the Connection manager type list select OLEDB and click Add. Then the Configure OLE DB Connection Manager dialog box is displayed, and after this you need to perform the steps above.

After the two required connections are created, we can proceed to customizing the data flow items.

For the sake of this example, we will also create a variable, for this:

1. Right-click the design surface and in the context menu select the Variables item.

   The Variables tool window is displayed.

2. Click the Add Variable button in the toolbar of the Variables tool window.
3. A new variable is created and displayed in the tool window. Rename it as required, then go to the Properties tool window and set the variable properties.

   In this example we call the variable 'myvar' and set the following properties:

   • ValueType = Int32
   • Value = 7007

   other properties are left set to their default values.

Now we proceed to customizing the Devart Oracle Source.

Double-click the Devart Oracle Source data flow object.

The Devart Oracle Source Editor dialog box is displayed.

Drag the required table (in our example we use the Products table from the dotConnect for Oracle sample crm_demo database) from the Oracle Objects area to the Query area:

Then we add a WHERE clause and drag the variable we have previously created from the User node of the Variables area to the Query area, thus we get the following query:

Click the Preview Data button to view the result of the query execution. The Preview Data window is displayed:

Click Close to return to the Devart Oracle Source Editor dialog box.

In the editor dialog box click OK.

If all options are set correctly, the red marker on the data flow object disappears.

Drag the OLE DB Destination data flow object to the design area.

Connect the two data flow objects with a Data Flow path.

Proceed to customizing the OLE DB Destination.

Right-click the OLE DB Destination data flow object. The Connection Manager tab of the OLE DB Destination Editor dialog box is displayed.

Specify the following settings in this tab:

• Select the required connection in the OLE DB connection manager drop-down list;
• Select Table or View in the Data access mode drop-down list;
• Select the required database object name in the Name of the table or the view drop-down list.

Switch to the Mappings tab.

This tab displays mapping defined automatically by SSIS on the basis of the column name correspondence. If necessary, specify mapping between the source and destination columns either using the Input Columns drop-down lists, or just by drawing lines between the corresponding columns:

Switch to the Error Output tab.

This tab allows you to specify in what way component failure should be treated; the following options are available: Fail component, Redirect row, Ignore failure.

After all options are set, click OK.

Make sure that the red marker is absent on the OLE DB Destination data flow object.

Right-click the design area and select Execute Task in the context menu. The green color of the data flow objects indicates that the data flow process is completed successfully:

See Also

Source Overview