Loading Data From Salesforce

In This Topic

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

In the Control Flow tab, go to the Toolbox docking window, in the Control Flow Items list select Data Flow Task and drag it to the design area. The Data Flow Task is created.

Switch to the Data Flow tab, go to the Toolbox docking window, in the Data Flow Sources list select Devart Salesforce Source and drag it to the design area.

Then, go to the Toolbox docking window again, and in the Data Flow Destinations list select OLE DB Destination and drag it to the design area.

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

Right-click in the Connection Managers pane and select New Connection from the context menu. The Add SSIS Connection Manager dialog box is displayed:
In the Connection manager type list select DevartSalesforce and click Add. The General tab of the Salesforce Connection Manager Editor dialog box is displayed:
In the Login Information area, specify the User name (email address) and Password you have specified, and Security Token you have received when registering at the Salesforce website; in the Proxy Information area, specify the required Host and Port; in the Authorization area, specify the User name and Password and click OK.

The connection for the Salesforce database is now created.

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

Right-click in the Connection Managers pane and select New Connection from the context menu. The Add SSIS Connection Manager dialog box is displayed.
In the Connection manager type list select OLEDB and click Add. The Configure OLE DB Connection Manager dialog box is displayed. Click New. The Connection Manager dialog box is displayed; fill in the connection settings as required, e.g. as follows:

Click OK.
The Configure OLE DB Connection Manager dialog box is displayed again, click OK.

The connection for the OLE DB source is now created.

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:

Right-click the design surface and in the context menu select the Variables item.
The Variables docking window is displayed.
Click the Add Variable button in the toolbar of the Variables docking window.
A new variable is created and displayed in the docking window. Rename it as required, then go to the Properties docking window and set the variable properties.
In this example we call the variable 'myvar' and set the following properties:
- ValueType = String
- Value = GenePoint
other properties are left set to their default values.

Now we proceed to customizing the Devart Salesforce Source.

Double-click the Devart Salesforce Source data flow object.

The Devart Salesforce Source Editor dialog box is displayed.

Drag the required table (in our example we use the Account table) from the Salesforce Objects area to the Query area:

Then we remove the columns we don't need from the SQL query, 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 Salesforce 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