Microsoft Azure Synapse SQL Connector > Mappings and mapping tasks with Microsoft Azure Synapse SQL > Microsoft Azure Synapse SQL target properties
  

Microsoft Azure Synapse SQL target properties

In a mapping, you can configure a Target transformation to represent a single Microsoft Azure Synapse SQL object. When the source is partitioned and you write data to Microsoft Azure Synapse SQL, the Secure Agent uses the pass-through partitioning to optimize the mapping performance at run time.
The following table describes the Microsoft Azure Synapse SQL properties that you can configure in a Target transformation:
Property
Description
Connection
Name of the target connection. Select a target connection or click New Parameter to define a new parameter for the target connection.
When you switch between a non-parameterized and a parameterized Microsoft Azure Synapse SQL connection, the advanced property values are retained.
Target Type
Target type. Select one of the following types:
  • - Single Object
  • - Parameter. Select Parameter to define the target type when you configure the task.
Object
Name of the target object.
Parameter
Select an existing parameter for the target object or click New Parameter to define a new parameter for the target object. The Parameter property appears only if you select Parameter as the target type.
Create Target
Creates a target. Enter a name for the target object and select the source fields that you want to use. By default, all source fields are used.
You cannot parameterize the target at runtime.
Operation
The target operation.
Select one of the following options:
  • - Insert
  • - Update
  • - Upsert
  • - Delete
  • - Data Driven1. Select to honor flagged rows for an insert, update, delete, or reject operation from the Update Strategy transformation or a CDC source.
You can also use the Treat Source Rows As advance property to perform insert, delete, update, or upsert operations.
However, if you want to define a key for the delete, update, or upsert operation, you must use the Operation property.
For more information about the operations, see Rules and guidelines for mappings and mapping tasks.
Data Driven Condition1
Enables you to define expressions that flag rows for an insert, update, delete, or reject operation.
Note: Appears only when you select Data Driven as the operation type. However, you may leave the field empty as the rows in the Update Strategy transformation and CDC source tables are already marked with the operation types.
Update Column
The key columns to upsert or update data to or delete data from Microsoft Azure Synapse SQL. This property is honored only if you select delete, update, or upsert operation in the Treat Source Rows As advance property.
Note: This property appears only if you select delete, update, upsert, or data driven operation in the Operation property.
1Applies only to mappings.
The remaining properties are applicable for both mappings and elastic mappings.
The following table describes the Microsoft Azure Synapse SQL properties that you can configure in a Target transformation:
Advanced Property
Description
Azure Blob Container Name
Microsoft Azure Blob Storage container name. Required if you select Azure Blob storage in the connection properties.
ADLS FileSystem Name
The name of the file system in Microsoft Azure Data Lake Storage Gen2. Required if you select ADLS Gen2 storage in the connection properties.
You can also provide the path of the directory under given file system.
Copy Method1
The method to load data from the staging location to Microsoft Azure Synapse SQL. Select one of the following options:
  • - Polybase
  • - Copy Command
Default is Polybase.
You can use copy command only to insert CSV files to Microsoft Azure Synapse SQL.
Copy Command Options1
Options for the copy command in key=value format.
Specify each option on a new line.
For more information on copy command options, see Copy command.
Schema Name Override
Overrides the schema specified in the connection.
Table Name Override
Overrides the table name of the imported Microsoft Azure Synapse SQL target table.
Field Delimiter
Character used to separate fields in the file. Default is 0x1e. You can specify 'TAB' or 0-256 single-char printable and non-printable ASCII characters. Non-printable characters must be specified in hexadecimal.
Note: Multi-char ASCII characters except TAB are not supported. You cannot use the following non-printable characters:
00x0, 0x0, 0x0A , 0x1B, 0x0D, and 0x1F
Number of Concurrent Connections to Blob Storage1
Number of concurrent connections to extract data from the Microsoft Azure Blob Storage. When reading a large-size blob, you can spawn multiple threads to process data.
Default is 4. Maximum is 10.
Truncate Table
Truncates the target before inserting data to the target.
Pre-SQL
Pre-SQL command that must be run before reading data from the source.
Post-SQL
Post-SQL command that must be run after writing data to the target.
On Pre-Post SQL Error
Determines the behavior when a task that includes pre-SQL or post-SQL commands encounters errors. You can select any of the following options:
  • - Continue. The task continues regardless of errors.
  • - Stop. The task stops when errors occur while executing pre-SQL or post-SQL commands.
Treat Source Rows As
Select one of the following options:
  • - NONE1,2
  • - INSERT1,2
  • - DELETE1,2
  • - UPDATE1,2
  • - UPSERT1,2
  • - DATA DRIVEN1: select to honor the flagged rows from the update strategy or any other custom transformation, or a CDC source.
  • Default is None.
    Data Driven does not apply to elastic mappings.
Batch Size1
Minimum number of rows in a batch. Enter a number greater than 0.
Default is 2000000.
Reject Threshold
Number of errors within a batch that causes a batch to fail. Enter a positive integer.
If the number of errors is equal to or greater than the property value, the Secure Agent rejects the entire batch to the error file and marks the session failed.
Note: When you do not set the reject threshold, the mapping fails when an error is encountered.
Quote Character
Specifies the quote character to skip when you write data to Microsoft Azure Synapse SQL. When you write data to Microsoft Azure Synapse SQL and the source table contains the specified quote character, the task fails. Change the quote character value to a value that does not exist in the source table.
Compression Format1
Compresses the staging files in the .Gzip format. Default is None.
Update Override1
Overrides the default update SQL statement that the Secure Agent generates.
Interim Directory1
Optional. Path to the staging directory in the Secure Agent machine.
Specify the staging directory where you want to stage the files when you write data to Microsoft Azure Synapse SQL. Ensure that the directory has sufficient space and you have write permissions to the directory.
Default staging directory is /tmp.
You cannot specify an interim directory for elastic mappings.
You cannot specify an interim directory when you use the Hosted Agent.
Forward Rejected Rows
Determines whether the transformation passes rejected rows to the next transformation or drops rejected rows. By default, the mapping task forwards rejected rows to the next transformation.
1Applies only to mappings.
The remaining properties are applicable for both mappings and elastic mappings.

Copy command

You can use the copy command to load data from Microsoft Azure Blob Storage or Microsoft Azure Data Lake Storage Gen2 to Microsoft Azure Synapse SQL.
When you stage files in Microsoft Azure Data Lake Storage Gen2, you can use the copy command only with Service Principal Authentication.
You can specify the options for the copy command in key=value format in the Copy Command Options field. Specify each option in a new line.
The following image shows an example of the copy command options:
The image shows an example of the copy command options:
You can set the following copy command options:
ERRORFILE
Specifies the directory where you want to write rejected rows and the corresponding error file. The ERRORFILE option is equivalent to the Reject Directory advanced target property.
Use the following format: ERRORFILE=<Directory Location>.
You can specify the absolute path or relative path for the directory.
If you specify an absolute path, for example, Dir1/Dir2, the agent creates the reject directory in the following path:
Dir1/Dir2
If you specify a relative path, for example, /Dir1/Dir2, the agent creates the reject directory in the following path:
<staging path>/Dir1/Dir2
If you do not specify the directory, the agent creates the reject directory in the following path:
<staging path>/Reject_<UUID Randomly Generated>
MAXERRORS
Specifies the maximum number of reject rows allowed in the load before the copy command is canceled. Each row that cannot be imported by the copy command is ignored and counted as one error. The MAXERRORS option is equivalent to the Reject Threshold advanced target property.
Use the following format: MAXERRORS=max_errors. For example, MAXERRORS=20.
COMPRESSION
Specifies the data compression method for the data. You can use only Gzip compression for CSV files.
The COMPRESSION option is equivalent to the Compression Format advanced target property.
Use the following format: COMPRESSION='method'. For example, COMPRESSION='GZIP'.
FIELDQUOTE
Specifies a single character that is used as the quote character in the CSV file.
The FIELDQUOTE option is equivalent to the Quote Character advanced target property.
Use the following format: FIELDQUOTE='field_quote'.
FIELDTERMINATOR
Specifies the field terminator that is used in the CSV file.
The FIELDTERMINATOR option is equivalent to the Field Delimiter advanced target property.
Use the following format: FIELDTERMINATOR='field_terminator'. For example, FIELDTERMINATOR='ox1f'.

Rejected rows

The Secure Agent generates two error files in the Microsoft Azure Blob container specified in the target properties.
The error files are generated with the keyword Reject as a prefix in the error file names. One error file contains an entry for each rejected row and the other error file lists the cause for the rejected rows. To generate the error files, specify Reject Threshold in advanced target properties.

Mapping tasks with CDC sources

Your organization needs to replicate real-time changed data from a mission-critical Oracle production system to minimize intrusive, non-critical work, such as offline reporting or analytical operations system. You can use Microsoft Azure Synapse SQL Connector to capture changed data from the Oracle CDC source and write the changed data to a Microsoft Azure Synapse SQL. Add the Oracle CDC sources in mappings, and then run the associated mapping tasks to write the changed data to the target.
    1. In Data Integration, click New > Mapping > Create.
    The New Mapping dialog box appears.
    2. Enter a name and description for the mapping.
    3. On the Source transformation, specify a name and description in the general properties.
    4. On the Source tab, select the configured Oracle CDC connection and specify the required source properties.
    5. On the Target transformation, specify a name and description in the general properties.
    6. On the Target tab, perform the following steps to configure the target properties:
    1. a. In the Connection field, select the Microsoft Azure Synapse SQL connection.
    2. b. In the Target Type field, select the type of the target object.
    3. c. In the Object field, select the required target object.
    4. d. In the Operation field, select Data Driven to properly handle insert, update, and delete records from the source.
    5. e. In the Data Driven Condition field, leave the field empty.
    6. f. In the Update Column field, select the key columns to upsert or update data to or delete data from Microsoft Azure Synapse SQL. This property is honored only if you select delete, update, or upsert operation in the Treat Source Rows As advance property.
    7. g. In the Advanced Properties section, you must select Data Driven in the Treat Source Rows As property.
    7. On the Field Mapping tab, map the incoming fields to the target fields. You can manually map an incoming field to a target field or automatically map fields based on the field names.
    8. In the Actions menu, click New Mapping Task.
    The New Mapping Task page appears.
    9. In the Definition tab, enter the task name and select the configured mapping.
    10. In the CDC Runtime tab, specify the required properties.
    For more information about the CDC Runtime properties, see the Informatica Cloud Data Integration Oracle CDC Connector User Guide.
    11. In the Schedule tab, specify the following properties in the Advanced Session Properties section:
    1. a. In the Commit on End of File field, select the value of the property as No.
    2. b. In the Commit Type field, select the value of the property as Source.
    3. c. In the Recovery Strategy field, select the value of the property as Resume from last checkpoint.
    12. Click Save > Run the mapping.
    Alternatively, you can create a schedule that runs the mapping task on a recurring basis without manual intervention. You can define the schedule to minimize the time between mapping task runs.
In Monitor, you can monitor the status of the logs after you run the task.

Changed data capture limitations

Consider the following limitations when working with a Microsoft Azure Synapse SQL change data capture (CDC) target:

Bulk processing for write operations

You can enable bulk processing to write large amounts of data to Microsoft Azure Synapse SQL. Bulk processing utilizes minimal number of API calls and the performance of the write operation is optimized.
To enable bulk processing, specify the property -DENABLE_WRITER_BULK_PROCESSING=true in the Secure Agent properties:
Perform the following steps to configure bulk processing before you run a mapping:
  1. 1. In Administrator, select the Secure Agent listed on the Runtime Environments tab.
  2. 2. Click Edit.
  3. 3. In the System Configuration Details section, select Data Integration Server as the service and DTM as the type.
  4. 4. Edit the JVM option, and enter -DENABLE_WRITER_BULK_PROCESSING=true.
  5. 5. Click Save.
You cannot use bulk processing for write operations in an elastic mapping.
You cannot use bulk processing in mapping tasks configured with pushdown optimization using the Microsoft Azure Synapse SQL ODBC connection or the Microsoft Azure Synapse SQL connection.

Rules and guidelines for configuring update override

Consider the following rules and guidelines when you use the update override property for a Microsoft Azure Synapse SQL target: