NEW: Check our new hosted service COZYROC Cloud ...
Database Destination
Overview
The Database Destination Component is an SSIS Data Flow Component for loading and updating data into a database table. The component can be configured to insert, update, delete or upsert in row-by-row or bulk-load mode for ADO.NET, OLE DB and ODBC connections (see a list of supported databases below). Custom setup and teardown logic is supported via optional pre and post execute SQL commands. Error redirection is available in row-by-row mode and for ODBC bulk-load.
The availability in version 2.0 of the Database Destination Component makes obsolete the following SSIS+ destination components:
- ODBC Destination
- Oracle Destination
- DB2 Destination
- Informix Destination
- Sybase Destination
- Teradata Destination
Supported databases
The component allows usage of three kinds of database connections - ADO.NET, OLE DB and ODBC. Most of them support an optimized bulk-load mode, all support the regular row-by-row data processing.
| Database | ADO.NET | OLE DB | ODBC | Speed |
|---|---|---|---|---|
| SQL Server | regular and bulk | regular and bulk | regular and bulk | |
| Oracle | regular and bulk | regular | regular and bulk | 10-30x |
| Teradata | regular | regular | regular and bulk | 80x |
| PostgreSQL | regular and bulk | - | regular and bulk | 10x |
| DB2 | regular and bulk | regular | regular | 20x |
| MySQL | regular | regular | regular and bulk | 30x |
| Informix | regular and bulk | regular | regular | 6-8x |
| SAP ASE | regular and bulk | - | regular and bulk |
Editor
General Page
In this page the general parameters are configured.
Pre&Post Scripts Page
In this page the pre and post execute SQL commands are configured.
Efficient batch operations
When batch mode is enabled (i.e. when the batch size is specified to a number > 1), an automatically created temporary table is used to speed up the processing. Data is bulk inserted in this table and then the destination table is updated depending on the selected action with a one or more SQL commands. The temporary table is removed in the post-execute phase.
NOTE SAP ASE ADO.NET driver supports bulk-load only in an empty table. An intermediate automatically created temporary table is used. Rows are bulk-loaded in a temporary temp table, then they are transferred to the destination table using SQL command. The temporary table is removed in the post-execute phase.
Identity Insert
SQL Server and SAP ASE permits inserting value in an identity column. SAP ASE permits updating an identity column. When the destination table has an identity column
These modes are supported. Need to be explicitly enabled with the Identity Insert option.
Remarks
Because of a bug in SAP ASE ADO.NET driver unsigned smallint, unsigned int, unsigned bigint and smallmoney field types cannot be used.
When used SAP ASE ODBC driver decimal or numeric field types are not working. We have sent the following incident cases: 11553913, 11553762, 11553757.
PostgreSQL ODBC bulk mode is array insert. When complex types are used the loading speed will be slower. Each additional complex column will slow down further.
Tested Databases
As of now, the component has been tested with the following databases (depending on database version compatibility, it will work on previous and future versions):
- SQL Server 2017
- Oracle 18c
- IBM DB2 11.5
- IBM Informix 14.10
- MySql 8.0
- SAP ASE 16.0
- Teradata Express 16.20
- PostgreSQL 12.3
Demonstration
Quick Start
In this quick-start we will setup Database Destination to make upsert in a database table. Bulk-copy mode will be used with the default batch size of 200 rows.
To try upsert you can use the demo package from this archive. You should create a TEST_TABLE table with the following columns (the archive contains script to do that) :
- ID
INT - DEALDATE
DATETIME2 - PRICE
FLOAT - DESCRIPTION
NVARCHAR2 (250) - AMOUNT
DECIMAL (16)
The NumberOfRows variable controls the number of rows used in the insert and in the upsert phase
Select connection
Let's start by selecting an existing ADO.NET, OLE DB or ODBC connection manager. To use bulk-mode the connection manager must support it.
Select action
Select Upsert action. Additional parameters Primary Key and Identity Columns are displayed.
Select table
We will use the dbo.TEST_TABLE.
Select primary key
Select ID as а primary key.
Map columns
Go to Mappings page to confirm automatically created mappings.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing ADO.NET, OLE DB or ODBC connection manager.
Related Topics: ADO.NET Connection Manager, OLE DB Connection Manager, ODBC Connection Manager
Specify the desired action. This parameter has the options listed in the following table.
Action Description Create Create new rows in the destination table. Update Update rows matching primary key in the destination table. Delete Delete rows matching primary key in the destination table. Upsert Update rows matching primary key, insert non-matching in the destination table. Specify the destination database table where the data is loaded, updated or deleted.
Specify one or more columns for primary keys. Used when action is Update, Delete or Upsert.
Specify which columns will not participate in insert or update during upsert.
Specify the number of rows to be sent as a batch. If the size is set to 1, the component uses regular insert.
Specify the number of seconds allowed for the operation to complete before it is aborted.
Specify if Identity Insert or Identity Update should be allowed. The table must have identity column. Identity Insert is supported on SQL Server and SAP ASE. Identity Update is supported on SAP ASE.
Specify how to handle rows with errors.
What's New
2.0 SR-1
- Fixed: Failed with error "Invalid object name 'SYS.OBJECTS'" when using case-sensitive database (Thank you, Annette).
- Fixed: Insert failed in the temporary table with identity column (Thank you, David).
- Fixed: Failed with error "Incorrect syntax near the keyword 'MERGE'" on machines with older driver (Thank you, Josh).
- Fixed: Destination table columns are no longer alphabetically sorted when initialized (Thank you, Mika).
- Fixed: Columns refresh overview displayed incorrect information (Thank you, Geoff);
2.0
- New: Introduced component.
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.