After Establishing a Connection to the data source, add the CData Cloudant destination component to the workflow to load data into Cloudant.

Writing to Cloudant in a Data Flow

Follow the steps below to connect to Cloudant and update data.

1. In the SSIS Toolbox, drag the CData Cloudant destination component into the Data Flow Task.
2. Connect the output of a source component to the CData Cloudant destination component.
3. Double-click the CData Cloudant destination component. The CData Cloudant Destination Editor dialog is displayed.
4. In the Connection Managers menu, select an available CData Cloudant connection manager, or create a new instance if one is not already available.
5. In the "Use a Table" option, select the table to update.
6. Select the data manipulation action. See below for more information on each action.
7. On the Mappings tab, configure the mappings from source to destination. By default, outputs from the source component are automatically mapped with the same name as the columns in the table you selected. You can further update these selections.

NOTE: Read-only columns are not visible among the destination columns since they cannot be written to.

Command Execution

When you execute the data flow, the component executes one of the following operations to update the destination table.

Insert

The component takes the mapped values and attempt to insert the data as new rows into the table. By setting the OutputKey property to True in the destination component's properties, you can retrieve the results of the insertion in the error output of the component with the 'Redirect row' error behavior.

Update

The component attempts to update an existing row based on the primary key provided. The primary key column must be mapped, and it must not be null. By setting the OutputKey property to True in the destination component's properties, you can retrieve the results of the update in the error output of the component with the 'Redirect row' error behavior.

Upsert

The component uses the primary key to decide if a row is to be inserted or updated. If the primary key column is mapped and it is not null, the component attempts to update an existing row based on the primary key provided. If the primary key is not mapped or if it is null, the CData Cloudant Destination Component attempts to insert the data as a new row. By setting the OutputKey property to True in the destination component's properties, you can retrieve the results of the upsert in the error output of the component with the 'Redirect row' error behavior.

Delete

The component attempts to delete an existing row based on the primary key provided. The primary key column must be mapped, and it must not be null.

Create New Table

A new table can be generated in the destination database by clicking the New... button next to the table dropdown. This opens a dialog with a generated CREATE TABLE query. The default table name is "CData Destination" and the column definition are taken from the input to the destination component. This query can be modified as desired, or you can supply your own custom query here. After you hit OK, the query is executed on the destination database.

NOTE: The generated SQL query is in the SQL Server dialect. It is recommended to have QueryPassthrough set to False in the connection manager when creating tables through this method.