Devart Destination components are designed for loading data into the corresponding data sources.
Note that Devart Destination components support only the corresponding Devart connection managers.
To add a Devart Destination component to a Data Flow task, perform the following steps:
| 1. | Drag the corresponding Devart Destination component from the SSIS Toolbox window to the Data Flow diagram. |
| 2. | Connect the necessary data flow path to it. |
| 3. | Double-click the added component to open its editor. |
| 4. | On the Connection Managers tab select the corresponding Devart Connection Manager or create a new one. |
| 5. | Click the Component Properties tab. |
| 6. | In the <Data Source> Object (for cloud sources) or <Data Source> Table (for databases) property type or select the target object or table (where <Data Source> is the corresponding data source). |
| 7. | In the Action property select the required DML operation to apply to the data source. Supported DML operations are described below. |
| 8. | Optionally configure other custom properties of the component. Custom properties are source-specific. They are described in the Custom Properties Reference section. For example, |
| 9. | Click the Column Mappings tab. |
| 10. | Map the input columns to the corresponding destination columns. The columns with the same names in input and destination are mapped automatically. |
|
Devart Destination components support all the three basic DML operations: insert, update, and delete. Destination components for some sources support additional operations.
| • | Insert - This is the most common operation. See a tutorial on using it. |
| • | Update - Devart Destination component in the Update mode enables updating data in a data source. See a tutorial on using it. Peculiarity of this action consists in the fact, that performing it requires using primary key values of the target table (the Id value for cloud sources). Thus, when performing an update, you must know and specify the primary key values of the target table to update a record. |
| • | Delete - Devart Destination component in the Delete mode enables deleting data from a data source. As well as for the update operation, you must know and specify the primary key values of the target table to delete a record. |
| • | Upsert - This operation is supported for Salesforce. When executing an upsert, records are inserted if they do not exist in the Salesforce object; in case records do exist, then data update is performed. This action is based on the External ID field.
In order to use the Upsert option, the External ID field must be added to the Salesforce object, and the local storage must contain the corresponding unique column (usually this is a primary key in the database table). When configuring the component, you must map this unique column to the External ID field in the target Salesforce object. |
| • | BulkInsert - This operation is supported for relational databases except DB2 and cloud data warehouses. It uses database-specific optimization techniques to speed up data loading. You can see the description of these techniques below. |
| • | HardDelete - This operation is supported for Salesforce. It completely deletes data rows without the possibility to restore them. In Salesforce, the Delete operation actually keeps the records and just sets their IsDeleted property to True, the HardDelete operation actually deletes the data. |
|
Performing an update or delete operation requires mapping for the target table primary key columns. A destination component determines a record to update or delete by their ID/primary key values. Thus, the updated table must have a primary key. For the UPDATE operation you also need to provide values for the columns you want to change the value of, and you can additionally map any target object columns. For the delete operation, it is enough to map only the target primary key.
When ID/Primary Key values are known and present in the source, you can simply map them to the corresponding target columns. However, in many cases there are no target primary key values in a source. In this case you may retrieve the target primary key values by some other identifying columns using Devart Lookup Components. You can see an example of update operation with using lookup in our Devart Lookup Tutorial - Inserting and Updating Contact Data.
|
Devart Destination components for relational database can use certain database-specific techniques to speed up data loading:
| • | Devart Oracle Destination uses direct path load interface to speed up data loading in the OCI connection mode. In the Direct connection mode it uses Array Binding - a feature that allows executing several INSERT SQL statements with the different parameters at once. Oracle server is accessed only once per a batch of inserted records, and that increases the speed a lot. See details on the Direct and OCI modes here. |
| • | Devart MySQL Destination uses INSERT statements that insert data by several rows at the same time. |
| • | Devart PostgreSQL Destination uses the COPY command. |
| • | Devart AzureDWH Destination uses PolyBase technique to speed up data loading. It uploads data as CSV files to Azure Blob Storage and tells Azure Synapse Analytics to import data. After the data is imported, the CSV files are deleted. |
| • | Devart Redshift Destination uploads data as CSV files to an Amazon S3 bucket and then tells Redshift to import data using the COPY command. After the data is imported, the CSV files are deleted. |
| • | Devart BigQuery Destination also uploads data as CSV files to a Google Cloud Storage bucket and tells BigQuery to import data. After the data is imported, the CSV files are deleted. |
|
Devart Destination components support error outputs, which allow you to manage how the component treats row-level errors in both incoming and outgoing data.
The error output includes the column set taking part in the data flow and additional columns, they are ErrorCode that identifies the error, ErrorColumn that contains the lineage identifier of the error column and ErrorDescription that provides a detailed description of the error.
The ErrorCode column can accept the following values:
ErrorCode
|
Description
|
-1
|
Means that the record is valid.
|
>0
|
Means that the record causes an error.
|
Additionally, when the primary key values are automatically generated for the target table, Devart Destination component retrieves the generated values and adds them to its output columns.
|
How to use Devart Destination Components