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
  • Flat File Destinations
  • Delimited File
  • Fixed-Length File
  • Excel File
  • Tree File Destinations
  • XML/JSON File
  • Database Destinations
  • Database Table
  • SQL Statement
  1. Dataflows
  2. Destinations

Setting Up Destinations

PreviousDestinationsNextDatabase Table Destination

Last updated 9 months ago

Each destination on the dataflow is represented as a destination object. You can have any number of destinations of the dataflow. Each destination can only receive data from a single source.

To feed multiple sources into a destination you need to connect them through a transformation object, for example, Merge or Union. For more information on Transformations, see the Creating Transformations article.

The following destination types are supported by the dataflow engine:

Flat File Destinations:

Tree File Destinations:

Database Destinations:

All destinations can be added to the dataflow by grabbing a destination type from the Toolbox and dropping it on the dataflow. File destinations can also be added by dragging and dropping a file from an Explorer window while pressing the ‘Shift’ key.

Database destinations can be dragged and dropped from the Data Source Browser while holding down the Shift Key. For more details on adding destinations, see the article.

Flat File Destinations

Delimited File

Adding a Delimited File Destination object allows you to write to a delimited file. An example of what a Delimited File Destination object looks like is shown below:

To configure the properties of a Delimited File Destination object after it was added to the dataflow, right-click on its header and select Properties from the context menu.

Fixed-Length File

Adding a Fixed-Length File Destination object allows you to write to a fixed-length file. An example of what a Fixed-Length File Destination object looks like is shown below:

To configure the properties of a Fixed-Length File Destination object after it was added to the dataflow, right-click on its header and select Properties from the context menu.

Excel File

Adding an Excel Workbook Destination object allows you to write to an Excel file. An example of what an Excel Workbook Destination object looks like is shown below:

To configure the properties of an Excel Workbook Destination object after it was added to the dataflow, right-click on its header and select Properties from the context menu.

Tree File Destinations

XML/JSON File

Adding an XML/JSON File Destination object allows you to write to an XML file. An example of what an XML/JSON File Destination object looks like is shown below:

To configure the properties of an XML/JSON File Destination Object after it was added to the dataflow, right-click on its header and select Properties from the context menu. The following properties are available:

  1. General Properties window:

    • File Path – Specifies the location of the destination XML file. Using UNC paths is recommended if running the dataflow on a server.

Note: To open an existing destination file for editing in a new tab, click the XXX icon next to the File Path input, and select Edit File.

  • File Options: Using the Encoding dropdown, select the appropriate encoding scheme for your destination file. Check the Format XML Output checkbox to have line breaks inserted into the destination XML file for improved readability.

  • Schema Options: Read From Schema File Specified Below – Specifies the location of the XSD file controlling the layout of the XML destination file.

Note: You can generate the schema based on the content of the destination XML file if the file already exists. The data types will be assigned based on the destination file’s content. Note that the existing destination file will be overwritten when the dataflow runs.

To generate the schema, click the icon next to the Schema File input, and select Generate.

To edit an existing schema, click the icon next to the Schema File input, and select Edit File. The schema will open for editing in a new tab.

Using the Root Element dropdown, select the node that should be the root of your destination schema. Any nodes up the tree will be excluded.

Note: To ensure your dataflow is runnable on a remote server, please avoid using local paths for the destination. Using UNC paths is recommended.

Database Destinations

Database Table

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 its header and select Properties from the context menu. The following properties are available:

  1. Destination Connection screen – Allows you to enter the connection information for your destination, such as Server Name, Database, and Schema, and credentials for connecting to the selected destination.

  2. Pick Table window:

    • 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: This option is only available if you have assigned at least one field in your destination layout as a 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.

    • Data Load Options: specify the type of insert of your records into a destination database. The available types are Use Single Record Insert, Bulk Insert with Batch Size, and Bulk Insert with All Records in One Batch. These types allow you to customize your transfer to balance performance vs. logging needs. Bulk inserts typically result in a better performance (faster transfer for a given number of records), but they also come with less logging, and less ability to undo unwanted inserts should you need to.

      • Use 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 quick method of transferring large amounts of data. But keep in mind that, should there be any database-specific errors in your transfer, they will not 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 Insert.

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”.

SQL Statement

SQL Statement Destination object offers extra flexibility over database destination objects in applying a custom INSERT or UPDATE SQL code that controls what will be written into the destination table. An example of what an SQL Statement Destination object looks like is shown below.

To configure the properties of an SQL Statement Destination object after it was added to the dataflow, right-click on its header and select Properties from the context menu. The following properties are available:

  1. Database Connection window – Allows you to enter the connection information for your SQL Statement, such as Server Name, Database, and Schema, as well as credentials for connecting to the selected database.

  2. SQL Query window: You can enter an SQL expression controlling which fields and records should be written to the destination. The SQL expression should follow standard SQL syntax conventions for the chosen database provider.

For example,

Insert into Orders values (@OrderId, “@OrderName”, “@CreatedDtTm”)

Notice the @ symbol in front of a field name. This makes the field appear in the field list inside the object box so that the field can be mapped. The fields not showing the @ symbol in front of them will not show in the list of fields, but they can still receive values according to the logic of the SQL statement itself.

For example,

Insert into Orders (OrderId, OrderName, CreatedDtTm) values (@OrderId, “@OrderName”, “2010/01/01”)

Note: You can optionally use $ parameters inside your SQL expression.

Delimited File
Excel File
Fixed Length File
XML File
Database Table
SQL Statement
Introducing Dataflows