Database Table Destination

Astera Database Table Destination provides the functionality to write data to a database table. This component features a great deal of control over how data is written to the database table.

Astera supports a wide range of on-premises and cloud databases including SQL Server, Oracle, DB2, Sybase, MySql, database.com from Salesforce, and Microsoft Dynamics CRM and more. Astera delivers highly optimized implementations for these database connectors including high-performance bulk insert, set based updates and transaction management. This, combined with Astera's parallel processing architecture, delivers industrial strength performance and scalability.

Transaction management

Database Table Destination provides full support for transaction management including rollback on job level or record level errors. Moreover, using the Shared Connection object, you can link multiple destinations to a single connection and transaction. This gives you control over transaction rollback based on errors in any of the destinations.

Database Write Strategies

Astera Database Write Strategies transformations work in conjunction with Database Table Destination to provide change data capture (CDC) functionality as well as provide specialized transformations such as Type 1 and Type 2 slowly changing dimension management. These transformations enable you to create powerful database integrations with a straightforward user interface.

Steps

Adding a database table destination object allows you to write to a database table. An example of what a database table destination object looks like is shown below.

To configure the properties of a Database Table Destination object after it was added to the dataflow, right-click on it and select Properties from the context menu. The following properties are available:

  • Destination Connection screen – allows you to enter the connection information for your destination, such as server name, database and schema, as well as credentials for connecting to the selected destination.

  • Pick Table screen:

    • Database Transaction Management: Enable Transaction Management if you want to wrap your transfer inside a transaction. Depending on your database settings, this can give you performance improvements during the transfer. When Transaction Management is enabled, you should choose between always committing transaction at the end of the transfer, or only committing it if there were no errors. Any errors would result in the entire transaction being rolled back.

    • Preserve System Generated Key Values From Source: This option is only available if you assigned at least one field in your destination layout as System Generated field. If enabled, Astera will pass the incoming value from the source to the system generated field. Otherwise, the incoming source value will be ignored, and the system will write auto-increasing values to the destination System Generated field.

    • Database Load Options: specify the type of insert of your records into a destination database. The available types are Single Record Insert, Bulk Insert with Batch Size, and Bulk Insert with All Records in One Batch.

      • Single Record Insert: Records are inserted into a destination table one-by-one. Performance is the slowest among the three insert types. However, any errors or warnings during the transfer are displayed to you immediately as the transfer progresses.

      • Bulk Insert with All Records in One Batch – typically a fast method of transferring large amounts of data. But keep in mind that, should there be any database specific errors in your transfer, they won’t show until the end of the transfer at which time the entire batch were to be written to the destination database. Note: Not all database providers support this type of insert.

      • Bulk Insert with Batch Size – a good tradeoff between performance and logging needs. Records are inserted in batches of the specified size. Typically, larger batch sizes result in better transfer speeds; however, performance gains may be less with relatively large batch sizes. Note: Not all database providers support this type of insert.

Note: These types allow you to customize your transfer to balance performance vs. logging needs. Bulk inserts typically result in a better performance as they require less logging.

Note: Bulk insert may not be available if there are certain data types in a destination table. In this case the transfer will proceed as “single insert”.

Field Layout

The Field Layout screen is available in the properties of most objects on the dataflow to help you specify the fields making up the object. The table below explains the attributes you can set in the Field Layout screen:

The table below provides a list of all the attributes available for a particular layout type:

Last updated

© Copyright 2023, Astera Software