In a mapping, you can configure a Target transformation to represent a single Microsoft Azure Synapse SQL. 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.
If you want to overwrite the parameter at runtime, select the Allow parameter to be overridden at run time option when you create a parameter. When the task runs, the agent uses the parameters from the file that you specify in the task advanced session properties.
You can switch between a non-parameterized and a parameterized Microsoft Azure Synapse SQL connection. When you switch between the connections, 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.
If you want to overwrite the parameter at runtime, select the Allow parameter to be overridden at run time option when you create a parameter. When the task runs, the agent uses the parameters from the file that you specify in the task advanced session properties.
Create New at Runtime
Creates a Microsoft Azure Synapse SQL target at runtime.
Enter a name for the target object and select the source fields that you want to use. By default, all source fields are used.
Select Use Exact Source Field Names in Target to write special characters from the source column names to the target.
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.
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.
1Doesn't apply to mappings in advanced mode.
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
Required if you select Azure Blob storage in the connection properties.
The name of the container in Microsoft Azure Blob Storage.
The container name cannot contain special characters.
ADLS FileSystem Name
Required if you select ADLS Gen2 storage in the connection properties.
The name of the file system in Microsoft Azure Data Lake Storage Gen2.
The file system name cannot contain special characters.
You can also specify the path of the directory under the file system. Use only a forward slash to specify the directory path.
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.
Copy Command Options1
Options for the copy command in key=value format.
Specify each option on a new line.
Schema Name Override
Overrides the schema specified in the connection.
Table Name Override
Overrides the table name of the imported Microsoft Azure Synapse SQL Data Warehouse target table.
Staging File Format
Type of file format to use when you stage the files in Azure storage.
Select one of the following formats:
- Delimited Text
- Parquet
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 applicable. 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:
- NONE
- INSERT
- DELETE
- UPDATE
- UPSERT
- DATA DRIVEN1. Select to honor the flagged rows from the update strategy or any other custom transformation, or a CDC source.
Default is None.
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.
The quote character that you specify must not exist in the source table. If it exists, enter a different quote character value.
Compression Format1
Compresses the staging files in the .Gzip format. Default is None.
Update Override
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 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.
1Doesn't apply to mappings in advanced mode.
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:
You can configure only the following copy command options in the advanced target properties. The default value is considered for other copy command options supported by Microsoft Azure.
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. The error file path must not contain special characters.
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'.
For more details about the copy command options, see the Microsoft Azure documentation.
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.
1In Data Integration, click New > Mapping > Create.
The New Mapping dialog box appears.
2 Enter a name and description for the mapping.
3On the Source transformation, specify a name and description in the general properties.
4On the Source tab, select the configured Oracle CDC connection and specify the required source properties.
5On the Target transformation, specify a name and description in the general properties.
6On the Target tab, perform the following steps to configure the target properties:
aIn the Connection field, select the Microsoft Azure Synapse SQL connection.
bIn the Target Type field, select the type of the target object.
cIn the Object field, select the required target object.
dIn the Operation field, select Data Driven to properly handle insert, update, and delete records from the source.
eIn the Data Driven Condition field, leave the field empty.
fIn 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.
gIn the Advanced Properties section, you must select Data Driven in the Treat Source Rows As property.
7On 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.
8In the Actions menu, click New Mapping Task.
The New Mapping Task page appears.
9In the Definition tab, enter the task name and select the configured mapping.
10In 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.
11In the Runtime Options tab, specify the following properties in the Advanced Session Properties section:
aIn the Commit on End of File field, select the value of the property as No.
bIn the Commit Type field, select the value of the property as Source.
cIn the Recovery Strategy field, select the value of the property as Resume from last checkpoint.
12Click 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:
•The staging temporary tables are not dropped if a mapping task is terminated. You must clean up the temporary tables manually.
•The following keywords are not allowed for the CDC target:
- Keywords not allowed in the table name: "(", " SET ", " WHERE ", "."
Keywords not allowed in column name: ") VALUES", ",", " SET ", " WHERE ", " = ?", " AND "
Note: Consider the leading and trailing spaces with certain keywords.
•You cannot capture changed data for datatimeoffset and time data type.
•Date conversion from other data types to smalldatetime is not applicable.
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:
1In Administrator, select the Secure Agent listed on the Runtime Environments tab.
2Click Edit.
3In the System Configuration Details section, select Data Integration Server as the service and DTM as the type.
4Edit the JVM option, and enter -DENABLE_WRITER_BULK_PROCESSING=true.
5Click Save.
You cannot use bulk processing in write operations for mappings in advanced mode.
You cannot use bulk processing in mapping tasks configured with SQL ELT 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:
•Select Update for the Treat source rows as property in the advanced target properties.
• Specify the update query in the following format:
UPDATE <Target table name> SET <Target table name>.<Column1> = :TU.<Column1>, <Target table name>.<Column2> = :TU.<Column2>, … <Target table name>.<ColumnN> = :TU.<ColumnN> FROM :TU WHERE <Target table name>.<Update Column1> = :TU.<Update Column1> AND <Target table name>.<Update Column2> = :TU.<Update Column2> AND … <Target table name>.<Update ColumnN> = :TU.<Update ColumnN>
•Column names for :TU must match the target table column names.
•You must specify the update query with a valid SQL syntax because Microsoft Azure Synapse SQL Connector replaces :TU with a temporary table name and does not validate the update query.
•You cannot change the order of the column mappings using the update override.
•You cannot use unconnected fields to the target in the update query.
