Astera Data Stack
Version 7
Version 7
  • Welcome to Astera Data Stack Documentation
  • Release Notes
    • Upgrading Astera 6 to Version 7
    • Release Notes for Astera 7.1
    • What is New in Astera 7.4
    • What’s New in Astera 7.4.1.221
    • What’s New in Astera 7.6
    • Upgrading to Astera 7.6
  • Setting Up
    • System Requirements
    • Installing Astera Data Integrator
    • Setting up Astera Integration Server 7
    • UI Walkthrough - Astera Data Integrator
  • Dataflows
    • Introducing Dataflows
    • Sources
      • Setting Up Sources
      • Raw Text Filters in File Sources
      • COBOL File Source
      • Database Table Source
      • Data Model Source
      • Delimited File Source
      • Email Source
      • Excel Workbook Source
      • File System Items Source
      • Fixed Length File Source
      • PDF Form Source
      • Report Source
      • SQL Query Source
      • XML/JSON File Source
    • Transformations
      • Aggregate Transformation
      • Apply To All Transformation
      • Constant Value Transformation
      • Data Cleanse Transformation
      • Data Quality Rules Transformation
      • Denormalize Transformation
      • Distinct Transformation
      • Expression Transformation
      • Filter Transformation
      • Function Transformations
      • Join Transformation
      • Merge Transformation
      • Normalize Transformation
      • Passthru Transformation
      • Rest Client
      • Route Transformation
      • Sequence Generator Transformation
      • Sort Transformation
      • Sources as Transformations
      • Subflow Transformation
      • Tree Join Transformation
      • Union Transformation
      • Web Service Transformation
    • Destinations
      • Setting Up Destinations
      • Database Table Destination
      • Delimited File Destination
      • Excel Workbook Destination
      • Fixed Length File Destination
      • SQL Statement Destination
      • XML/JSON File Destination
    • Data Logging and Profiling
      • Creating Data Profile
      • Creating Field Profile
      • Data Quality Mode
      • Record Level Log
      • Using Data Quality Rules in Astera
    • Database Write Strategies
      • Database Write Strategies
    • Text Processors
      • Fixed Length Parser
      • Fixed Length Serializer
  • Workflows
    • Adding Workflow Tasks
    • Creating Workflows
    • Using Workflow Designer
    • Customizing Workflows With Parameters
    • Workflows with a Dynamic Destination Path
    • Resuming and Rerunning Workflows in Astera
  • Subflows
    • Using Subflows
  • Functions
    • Functions Glossary
    • Introducing Function Transformations
    • Custom Functions
    • Logical
      • Coalesce (Any value1, Any value2)
      • IsNotNull (AnyValue)
      • IsRealNumber (AnyValue)
      • IsValidSqlDate (Date)
      • IsDate (AnyValue)
      • If (Boolean)
      • If (DateTime)
      • If (Double)
      • Exists
      • If (Int64)
      • If (String)
      • IsDate (str, strformat)
      • IsInteger (AnyValue)
      • IsNullOrWhitespace (StringValue)
      • IsNullorEmpty (StringValue)
      • IsNull (AnyValue)
      • IsNumeric (AnyValue)
    • Conversion
      • GetDateComponents (DateWithOffset)
      • ParseDate (Formats, Str)
      • GetDateComponents (Date)
      • HexToInteger (Any Value)
      • ToInteger (Any value)
      • ToDecimal (Any value)
      • ToReal (Any value)
      • ToDate (String dateStr)
      • TryParseDate (String, UnknownDate)
      • ToString (Any value)
      • ToString (DateValue)
      • ToString (Any data, String format)
    • Math
      • Abs (Double)
      • Abs (Decimal)
      • Ceiling (Real)
      • Ceiling(Decimal)
      • Floor (Decimal)
      • Floor (Real)
      • Max (Decimal)
      • Max (Date)
      • Min (Decimal)
      • Min (Date)
      • Max (Real)
      • Max (Integer)
      • Min (Real)
      • Pow (BaseExponent)
      • Min (Integer)
      • RandomReal (Int)
      • Round (Real)
      • Round (Real Integer)
      • Round (Decimal Integer)
      • Round (Decimal)
    • Financial
      • DDB
      • FV
      • IPmt
      • IPmt (FV)
      • Pmt
      • Pmt (FV)
      • PPmt
      • PPmt (FV)
      • PV (FV)
      • Rate
      • Rate (FV)
      • SLN
      • SYD
    • String
      • Center (String)
      • Chr (IntAscii)
      • Asc (String)
      • AddCDATAEnvelope
      • Concatenate (String)
      • ContainsAnyChar (String)
      • Contains (String)
      • Compact (String)
      • Find (Int64)
      • EndsWith (String)
      • FindIntStart (Int32)
      • Extract (String)
      • GetFindCount (Int64)
      • FindLast (Int64)
      • GetDigits (String)
      • GetLineFeed
      • Insert (String)
      • IsAlpha
      • GetToken
      • IndexOf
      • IsBlank
      • IsLower
      • IsUpper
      • IsSubstringOf
      • Length (String)
      • LeftOf (String)
      • Left (String)
      • IsValidName
      • Mid (String)
      • PadLeft
      • Mid (String Chars)
      • LSplit (String)
      • PadRight
      • ReplaceAllSpecialCharsWithSpace
      • RemoveChars (String str, StringCharsToRemove)
      • ReplaceLast
      • RightAlign
      • Reverse
      • Right (String)
      • RSplit (String)
      • SplitStringMultipleRecords
      • SplitStringMultipleRecords (2 Separators)
      • SplitString (3 separators)
      • SplitString
      • SplitStringMultipleRecords (3 Separators)
      • Trim
      • SubString (NoOfChars)
      • StripHtml
      • Trim (Start)
      • TrimExtraMiddleSpace
      • TrimEnd
      • PascalCaseWithSpace (String str)
      • Trim (String str)
      • ToLower(String str)
      • ToProper(String str)
      • ToUpper (String str)
      • Substring (String str, Integer startAt)
      • StartsWith (String str, String value)
      • RemoveAt (String str, Integer startAt, Integer noofChars)
      • Proper (String str)
      • Repeat (String str, Integer count)
      • ReplaceAll (String str, String lookFor, String replaceWith)
      • ReplaceFirst (String str, String lookFor, String replaceWith)
      • RightOf (String str, String lookFor)
      • RemoveChars (String str, String charsToRemove)
      • SplitString (String str, String separator1, String separator2)
    • Date Time
      • AddMinutes (DateTime)
      • AddDays (DateTimeOffset)
      • AddDays (DateTime)
      • AddHours (DateTime)
      • AddSeconds (DateTime)
      • AddMonths (DateTime)
      • AddMonths (DateTimeOffset)
      • AddMinutes (DateTimeOffset)
      • AddSeconds (DateTimeOffset)
      • AddYears (DateTimeOffset)
      • AddYears (DateTime)
      • Age (DateTime)
      • Age (DateTimeOffset)
      • CharToSeconds (Str)
      • DateDifferenceDays (DateTimeOffset)
      • DateDifferenceDays (DateTime)
      • DateDifferenceHours (DateTimeOffset)
      • DateDifferenceHours (DateTime)
      • DateDifferenceMonths (DateTimeOffset)
      • DateDifferenceMonths (DateTime)
      • DatePart (DateTimeOffset)
      • DatePart (DateTime)
      • DateDifferenceYears (DateTimeOffset)
      • DateDifferenceYears (DateTime)
      • Month (DateTime)
      • Month (DateTimeOffset)
      • Now
      • Quarter (DateTime)
      • Quarter (DateTimeOffset)
      • Second (DateTime)
      • Second (DateTimeOffset)
      • SecondsToChar (String)
      • TimeToInteger (DateTime)
      • TimeToInteger (DateTimeOffset)
      • ToDate Date (DateTime)
      • ToDate DateTime (DateTime)
      • ToDateString (DateTime)
      • ToDateTimeOffset-Date (DateTimeOffset)
      • ToDate DateTime (DateTimeOffset)
      • ToDateString (DateTimeOffset)
      • Today
      • ToLocal (DateTime)
      • ToJulianDate (DateTime)
      • ToJulianDayNumber (DateTime)
      • ToTicks (Date dateTime)
      • ToTicks (DateTimeWithOffset dateTime)
      • ToUnixEpoc (Date dateTime)
      • ToUtc (Date dateTime)
      • UnixTimeStampToDateTime (Real unixTimeStamp)
      • UtcNow ()
      • Week (Date dateTime)
      • Week (DateTimeWithOffset dateTime)
      • Year (Date dateTime)
      • Year (DateTimeWithOffset dateTime)
      • DateToJulian (Date dateTime, Integer length)
      • DateTimeOffsetUtcNow ()
      • DateTimeOffsetNow ()
      • Day (DateTimeWithOffset dateTime)
      • Day (Date dateTime)
      • DayOfWeekStr (DateTimeWithOffset dateTime)
      • DayOfWeek (DateTimeWithOffset dateTime)
      • DayOfWeek (Date dateTime)
      • DateToJulian (DateTimeWithOffset dateTime, Integer length)
      • DayOfWeekStr (Date dateTime)
      • FromJulianDate (Real julianDate)
      • DayOfYear (Date dateTime)
      • DaysInMonth(Integer year, Integer month)
      • DayOfYear (DateTimeWithOffset dateTime)
      • FromUnixEpoc
      • FromJulianDayNumber (Integer julianDayNumber)
      • FromTicksUtc(Integer ticks)
      • FromTicksLocal(Integer ticks)
      • Hour (Date dateTime)
      • Hour (DateTimeWithOffset dateTime)
      • Minute (Date dateTime)
      • JulianToDate (String julianDate)
      • Minute (DateTimeWithOffset dateTime)
      • DateToIntegerYYYYMMDD (DateTimeWithOffset dateTime)
      • DateToIntegerYYYYMMDD (Date dateTime)
    • Files
      • AppendTextToFile (String filePath, String text)
      • CopyFile (String sourceFilePath, String destFilePath, Boolean overWrite)
      • CreateDateTime (String filePath)
      • DeleteFile (String filePath)
      • DirectoryExists (String filePath)
      • FileExists (String filePath)
      • FileLength (String filePath)
      • FileLineCount (String filePath)
      • GetDirectory (String filePath)
      • GetEDIFileMetaData (String filePath)
      • GetExcelWorksheets (String excelFilePath)
      • GetFileExtension (String filePath)
      • GetFileInfo (String filePath)
      • GetFileName (String filePath)
      • GetFileNameWithoutExtension (String filePath)
      • LastUpdateDateTime (String filePath)
      • MoveFile (String filePath, String newDirectory)
      • ReadFileBytes (String filePath)
      • ReadFileFirstLine (String filePath)
      • ReadFileText (String filePath)
      • ReadFileText (String filePath, String codePage)
      • WriteBytesToFile (String filePath, ByteArray bytes)
      • WriteTextToFile (String filePath, String text)
    • Date Time With Offset
      • ToDateTimeOffsetFromDateTime (dateTime String)
      • ToUtc (DateTimeWithOffset)
      • ToDateTimeOffsetFromDateTime
      • ToDateTimeOffset (String dateTimeOffsetStr)
      • ToDateTimeFromDateTimeOffset
    • GUID
      • NewGuid
    • Encoding
      • ToBytes
      • FromBytes
      • UrlEncode
      • UrlDecode
    • Regular Expressions
      • ReplaceRegEx
      • ReplaceRegEx (Integer StartAt)
    • TimeSpan
      • Minutes
      • Hours
      • Days
      • Milliseconds
    • Matching
      • Soundex
      • DoubleMetaphone
      • RefinedSoundex
  • Report Model
    • User Guide
      • Report Model Tutorial
    • Report Model Interface
      • Field Properties
      • Pattern Properties
      • Region Properties
      • Report Browser
      • Report Options
    • Use Cases
      • Applying Pattern to Line
      • Auto-Creating Data Regions and Fields
      • Auto-Parsing
      • Creating Multi-Column Data Regions
      • Floating Patterns and Floating Fields
      • How to Work with Microsoft Word (Doc/Docx) Files in a Report Model
      • How to Work With OCR Scanned Files in a Report Model
      • How to Work With PDF Scaling Factor in a Report Model
      • Line Count
      • Pattern Count
      • Pattern is a Regular Expression
    • Exporting Options
      • Exporting a Report Model
      • Exporting Report Model to a Dataflow
    • Miscellaneous
      • Astera Report Model: Performance on Different Hardware Settings and Case Complexities
      • Microsoft Word and Rich Text Format Support
      • Importing Monarch Models
  • Project Management
    • Project Management
      • Deployment
      • Parameterization
    • Job Scheduler
      • Scheduling and Running Jobs on a Server
  • Web Services
    • Configuring Google Drive API through REST Client in Astera
    • Connecting to Eloqua using Astera REST API
    • POSTing Data Using the REST Client
    • Using the REST Client to Download a Text File
  • Metadata Management
    • Lineage and Impact Analysis
  • Connectors and Providers
    • Setting Up Oracle ODP.Net Connectivity in Astera
    • Running Microsoft Access Database Engine with Astera
    • Oracle Client Tools Setup
    • Oracle Data Load Options
  • Miscellaneous
    • Job Trace Improvements
    • SmartMatch Feature
    • Synonym Dictionary File
    • Using the Data Source Browser in Astera
  • Best Practices
    • Astera Best Practices - Dataflows
    • Cardinality Errors FAQs
    • Overview of Cardinality in Data Modeling
Powered by GitBook

© Copyright 2025, Astera Software

On this page
  • Transaction management
  • Database Write Strategies
  • Steps
  • Field Layout
  1. Dataflows
  2. Destinations

Database Table Destination

PreviousSetting Up DestinationsNextDelimited File Destination

Last updated 9 months ago

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-premise and cloud databases including SQL Server, Oracle, DB2, Sybase, MySql, database.com from Salesforce, Microsoft Dynamics CRM, and more.

Astera delivers highly optimized implementations for these database connectors including high-performance bulk inserts, 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. For information, please see Database Write Strategies.

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 a 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 assign 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 - 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 is to be written to the destination database.

    • 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 Bulk Inserts.

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 a “single insert”.

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.

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.

Attribute

Description

Name

The system pre-fills this item for you based on the field header. Field names do not allow spaces. Field names are used to refer to the fields in the Expression Builder or tools where a field is used in a calculation formula.

Header

Represents the field name specified in the header row of the file. Field headers may contain spaces.

Data Type

Specifies the data type of a field, such as Integer, Real, String, Date, or Boolean.

Format

Specifies the format of the values stored in that field, depending on the field’s data type. For example, for dates you can choose between DD-MM-YY, YYYY-MM-DD, or other available formats.

Start Position

Specifies the position of the field’s first character relative to the beginning of the record.Note: This option is only available for fixed length layout type.

Length

Specifies the maximum number of characters allotted for a value in the field. The actual value may be shorter than what is allowed by the Length attribute.Note: This option is only available for fixed length and database layout types.

Column Name

Specifies the column name of the database table.Note: This option is only available in database layout.

DB Type

Specifies the database specific data type that the system assigns to the field based on the field's data. Each database (Oracle, SQL, Sybase, etc) has its own DB types. For example, Long is only available in Oracle for data type string.Note: This option is only available in database layout.

Decimal Places

Specifies the number of decimal places for a data type specified as real.Note: This option is only available in database layout.

Allows Null

Controls whether the field allows blank or NULL values in it.

Default Value

Specifies the value that is assigned to the field in any one of the following cases:- The source field does not have a value- The field is not found in the source layout- The destination field is not mapped to a source fieldNote: This option is only available in destination layout.

Sequence

Represents the column order in the source file. You can change the column order of the data being imported by simply changing the number in the sequence field. The other fields in the layout will then be reordered accordingly.

Description

Contains information about the field to help you remember its purpose.

Alignment

Specifies the positioning of the field’s value relative to the start position of the field. Available alignment modes are LEFT, CENTER, and RIGHT.Note: This option is only available for fixed length layout type.

Primary Key

Denotes the primary key field (or part of a composite primary key) for the table.Note: This option is only available in database layout.

System Generated

Indicates that the field will be automatically assigned an increasing Integer number during the transfer.Note: This option is only available in database layout.

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

Layout Type

Attributes Available

Source Delimited file and Excel worksheet

Name, Header, Data type, Format

Source Fixed Length file

Name, Header, Data type, Format, Start position, Length

Source Database Table and SQL query

Column name, Name, Data type, DB type, Length, Decimal places, Allows null

Destination Delimited file and Excel worksheet

Name, Header, Data type, Format, Allows null, Default value

Destination Fixed Length file

Sequence, Name, Header, Description, Data type, Format, Start position, Length, Allows null, Default value, Alignment

Destination Database Table

Column name, Name, Data type, DB type, Length, Decimal places, Allows null, Primary key, System generated