Using the Premium ADO.NET Destination Component

The Premium ADO.NET Destination Component utilizes the existing ADO.NET Connection Manager to facilitate writing to databases.

General Page

The General Page of the Premium ADO.NET Destination Component allows you to specify the general settings of the component.

Premium ADO.NET Destination - General Page

Connection Manager

The Premium ADO.NET Destination Component requires an ADO.NET connection manager. The component currently supports the following data providers:

  • SQL Client Data Provider
  • ODBC Data Provider
  • OLE DB Data Providers
  • Oracle Client Data Provider
  • ODP.NET Managed and Unmanaged
  • MySQL Client Data Provider
Create Table...

Opens The ADO.NET Table Creator.

ADO NET Desintation Table Creator

It auto-generates a command based on the selected Connection Manager and Input Columns to create a new table. You can further customize the command to suite your needs and then click the 'Execute Command' button. You will be informed if the command was executed successfully or not and the table you created will be selected in the Destination Table property.

Destination Table

The Destination Table drop-down displays a list of available tables for the database specified in the Connection Manager.

Action

The command you want to execute on the Destination Table. Available actions include:

  • Insert - Add records to the Destination Table.
  • Update - Update existing records in the Destination Table
  • Upsert - If the specified record exists in the Destination Table it is updated otherwise it is inserted.
  • Delete - Delete existing records from the Destination Table
  • Custom Command  - Allows you to write your own database command. When Custom Command action is selected you will notice a command text box and tree view will appear. The tree view contains a list of Input Columns, SSIS Variables, and Database Tables from the selected ADO.NET Connection Manager. Selecting a table will expand it with its columns. You can drag and drop the items in the tree view to the command text box to help construct the command. During runtime, the command is executed for each record in the Input. Column and Variable values are properly parameterized and prepared for the database.
Use Bulk

Enable bulk where supported to get an increase in performance. If you do not see the Use Bulk option, it simply means that the current ADO.NET Connection Manager does not support  Bulk works differently depending on the action:

  • Insert - Records are bulk inserted into the target table per SSIS buffer. Records directed to the default output have been successfully written.
  • Update, Upsert, or Delete - A temporary table is created for records to be bulk inserted into per SSIS buffer. Records directed to the default output have been successfully written to the temporary table but NOT yet written to the target table. When the last record is processed by the component a MERGE command is executed against the temporary table and the target table. In SQL there is an additional output called Bulk Action Result that describes what action was applied to each record when the MERGE command was executed.
Only Update/Upsert/Delete First Match

When performing a condition/key based action (Update, Upsert, or Delete) you can enable the First Match option. This makes it so that if the key matches multiple records only the first one is updated/deleted.

Use Identity Insert

Normally in SQL, you will receive an error when attempting to explicitly set the value of an IDENTITY column. Enable this option to avoid such errors.

Columns Page

The Columns Page of the Premium ADO.NET Destination Component allows you to map the columns from upstream components to fields of the specified Destination Table in the General Page. The Columns Page is not available when the selected action is Custom Command.

Premium ADO.NET Destination - Columns Page

  • Key Column - The key checkbox column is used when performing an update (Update or Upsert) where the selected fields represent key columns. These fields are used in the WHERE clause of the UPDATE statement. This column is not available when using INSERT because there is no condition, and not available when using DELETE because every column is a conditional/key field.
  • Input Column - Select an Input Column from an upstream component here.
  • Destination Table Column - This is the field you are writing data to.
  • Data Type - This column indicates the type of value for the current field.
  • Unmap - This column can be used to unmap the field from the upstream input column, or otherwise it can be used to map the field to an upstream input column by matching its name if the field is not currently mapped.

Error Handling Page

The Error Handling page allows you to specify how errors should be handled when they happen. 

Premium ADO.NET Destination - Error Handling Page

There are three options available. 

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When the Redirect rows to error output option is selected, rows that failed to be sent will be redirected to the 'Error Output' output of the Transformation Component. As indicated in the screenshot below, the blue output connection represent rows that were successfully sent, and the red 'Error Output' connection represents rows that were erroneous. The 'ErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by server or the component itself.

Premium ADO.NET Destination - Error Output