Microsoft Azure Synapse SQL Connector

Back Next

Microsoft Azure Synapse SQL targets in mappings

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 Driven¹. 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 Condition¹	Enables you to define expressions that flag rows for an insert, update, delete, or reject operation. 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. This property appears only if you select delete, update, upsert, or data driven operation in the Operation property.
¹Doesn'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 Method¹	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 Options¹	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.
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. 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 Storage¹	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 DRIVEN¹. Select to honor the flagged rows from the update strategy or any other custom transformation, or a CDC source. Default is None.
Batch Size¹	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. 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 Format¹	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 Directory¹	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.
¹Doesn't apply to mappings in advanced mode.