Microsoft Azure Synapse SQL Connector

Back Next

Optimize the staging performance for a mapping

Data Integration, by default, creates a flat file locally in a temporary folder to stage the data after reading from or before writing to Microsoft Azure Synapse SQL. You can set Data Integration to optimize the staging performance.

If you do not set the staging property, Data Integration performs staging without the optimized settings, which might impact the performance of the task.

Consider the following rules when you enable the staging property:

When you read multiple objects, you cannot optimize the staging performance.

If you run a mapping enabled for

SQL ELT optimization

, the mapping runs without

SQL ELT optimization

If the data contains timestamp data types with time zone, the job runs without staging the data in the local flat file.

If you configure a mapping in advanced mode, the mapping runs without staging the data in the local flat file.

When you use a custom query or SQL override with the Order By clause and the

AzureSynapseDisableStagingForSort

property is set to

true

, the mapping runs without staging the data in the local flat file.

When you read source data with decimal values and write to a Microsoft Azure Synapse SQL target and enable the staging property to optimize the staging performance, the decimal values in the target get truncated and rounded off to the nearest integer.

To avoid this issue, in the system configuration details section, select the Type as DTM for the Data Integration service, and set the value for

DisableInfaDoubleHandlingForStaging

as yes.

When you enable the staging optimization property for lookups configured to use persistent cache and define the cache file name prefix, the mapping fails at runtime for consecutive runs.

When you use Parquet as the staging file format, you cannot use the staging property to optimize the staging performance and the mapping fails with validation errors.

Enabling the Microsoft Azure Synapse SQL Connector to optimize the staging performance

Perform the following tasks to enable the Microsoft Azure Synapse SQL Connector to optimize the staging performance:

In Administrator, click

Runtime Environments

Edit the Secure Agent for which you want to set the property.

In the

System Configuration Details

section, select the

Service

Data Integration Server

and the type as

Tomcat

Set the value of the Tomcat property to the plugin ID of the Microsoft Azure Synapse SQL connector.

You can find the plugin ID in the manifest file located in the following directory:

<Secure Agent installation directory>/downloads/<Microsoft Azure Synapse SQL package>/CCIManifest

The following image shows the property set for the Secure Agent for the read, write, and lookup operations:

This image shows the staging properties that you can set for read, write, and lookup operations.

To set the property for multiple connectors, specify the plugin ID for each connector separated by a comma.

When you run the mapping, the flat file is created in the following directory in your machine:

C:\Windows\Temp\AzureSynapse\stage\<AzureSynapse_Target.txt>

You can check the session logs. If the flat file is created successfully, Data Integration logs the following message in the session log:

The INFA_DTM_STAGING is successfully enabled to use the flat file to create local staging files.

Rename Saved Search

Table of Contents

Microsoft Azure Synapse SQL Connector

Microsoft Azure Synapse SQL Connector

Optimize the staging performance for a mapping

Optimize the staging performance for a mapping

Enabling the Microsoft Azure Synapse SQL Connector to optimize the staging performance