Time zone for the Date and Timestamp data type fields in Parquet file format defaults to the Secure Agent host machine time zone.
When you run a mapping to read from or write to Microsoft Azure Synapse SQL and select Parquet as the staging format in the advanced source properties, the time zone defaults to the Secure Agent host machine time zone.
To change the Date and Timestamp to the UTC time zone, configure the JVMOptions in the Secure Agent.
Perform the following steps to configure the JVM options in the Secure Agent:
1Select Administrator > Runtime Environments.
2On the Runtime Environments page, select the Secure Agent for which you want to configure the JVMOptions.
3In the upper-right corner, click Edit.
4In the System Configuration Details section, select Data Integration Server as the service and DTM as the type.
5Edit the JVMOption field and set the value to -Duser.timezone=UTC.
6Click Save.
When the staging property is enabled, the mapping fails even though the rows are successfully written to the target.
When you enable the staging property in a mapping that reads from Microsoft Azure Synapse SQL, the mapping might fail with one of the following errors even though the rows are successfully written to the target:
•[FATAL] Error : HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: Error converting data type VARCHAR to DATETIME.
•[FATAL] Error : HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopExecutionException: Too long string in column [-1]: Actual len = [1015]. MaxLEN=[1000]
To resolve this issue, increase the reject threshold in the advanced target properties and run the mapping again.
[FATAL] Exception: com.microsoft.sqlserver.jdbc.SQLServerException: 100001;Failed to generate query plan.
When you use an ORDER BY clause in a custom query or SQL override, the mapping fails with the following error:
[FATAL] Exception: com.microsoft.sqlserver.jdbc.SQLServerException: 100001;Failed to generate query plan.
To use the ORDER BY clause in a query, you must configure the following property in the JVM options of the Secure Agent:
-DAzureSynapseDisableStagingForSort=true
If you use custom query in a Lookup transformation, you must perform the following additional tasks:
- Enable the Sorted Input option in the advanced lookup properties.
- The query must first contain the ORDER BY clause for the condition fields are included in the lookup condition in the ascending order and then the other fields in any desired order.
- When you configure the Lookup transformation to return multiple rows, the Return All Rows option does not return the values in the expected order.
Mapping failed with a Java heap space error
When you run a mapping that reads a large volume of data from or writes a large volume of data to Microsoft Azure Synapse SQL, the mapping fails with a Java heap space error.
Set the JVM options for type Tomcat JRE to increase the -Xms and -Xmx values in the system configuration details of the Secure Agent. You must then restart the Secure Agent.
[ERROR] com.informatica.cci.cloud.client.impl.CCIClientExceptionImpl: Invalid expression string for filter condition.
If you configure an uncached Lookup transformation to look up data from a Microsoft Azure Synapse SQL source and the source contains a column of DateTime or Timestamp data type, the mapping fails for certain values with the following error:
[ERROR] com.informatica.cci.cloud.client.impl.CCIClientExceptionImpl: Invalid expression string for filter condition.