Rules and guidelines for mappings and mapping tasks

Consider the following rules and guidelines for mappings and mapping tasks:

General rules and guidelines

- When you change the storage type in a Microsoft Azure Synapse SQL connection in an existing mapping, you must either refresh that mapping or create a new mapping to display the source and target properties for the updated storage type.
- When you use Microsoft Azure Data Lake Storage Gen2 or Microsoft Azure Blob Storage to stage files in Microsoft Azure Synapse SQL mapping, consider the following guidelines:

▪ You can specify the Microsoft Azure Data Lake Storage Gen2 file system name and Microsoft Azure Blob Storage container name at the connection level for all mappings that use the Microsoft Azure Synapse SQL connection. You can override file system name and container name specified in the connection from the advanced source or target properties.
▪ You must specify the file system name and container name either in the connection or in the advanced source or target properties. If you specify at both the places, the Secure Agent considers values that you specify in the advanced properties.

- When you use Microsoft Azure Data Lake Storage Gen2 to stage files in Microsoft Azure Synapse SQL mapping, consider the following guidelines:

▪ You can use managed identity authentication for Microsoft Azure Data Lake Storage Gen2 to stage files only when you run a mapping to read data from a Microsoft Azure Synapse SQL source or Microsoft Azure Data Lake Storage Gen2 source.
▪ If the object ID for the system assigned identity is reset in the Azure portal, you must delete and recreate the user.

- You can use role-based access control or access control lists to authenticate access to the storage account for Microsoft Azure Data Lake Storage Gen2. Consider the following rules and guidelines when you use managed identity authentication with role-based access control or access control lists:

▪ You cannot use both role-based access control and access control lists for the same storage account.
▪ When you use access control lists, assign the read, write, and execute permissions to Microsoft Azure Synapse SQL to access the file system.
▪ Assign read, write, and execute permissions to Microsoft SQL Server to access the file system.
▪ If you enable system assigned identity, assign the required role or permissions to the Azure virtual machine to run the mappings and tasks.

If you enable user assigned identity, assign the required role or permissions to the Azure virtual machine and the user assigned identity to run the mappings and tasks.

- When you connect to a Microsoft Azure Synapse SQL case-sensitive database and if a mapping uses multiple sources or targets, ensure that the sources or targets do not have the same name.
- When you specify the external data source, the container or file system used by the external data source is used for staging data. The container or file system specified in the connection properties or advanced properties is not considered for staging data.
- The session load summary in the session log is not captured for each commit interval.
- When you use an ODBC connection to connect to Microsoft Azure Synapse SQL, do not use the MERGE statement in the pre-SQL or post-SQL query. Else, the mapping task fails.
- When the source fields are empty, the Secure Agent writes the empty values as NULL in the target.
- When you use the $$$SESSSTARTTIME variable in an SQL query, the variable returns the session start time as a string value.

Use the following syntax to convert the string values to datetime:

SELECT CAST(CONVERT(VARCHAR,SUBSTRING('$$$SESSSTARTTIME',0,20),113) as datetime)

Use the following syntax to convert the string values to date:

SELECT CAST(CONVERT(VARCHAR,SUBSTRING('$$$SESSSTARTTIME',0,20),113) as date)

- When you use a custom query, SQL override, or update override for Uniqueidentifier fields, you must convert the Uniqueidentifier data type to string data type. Else, the mapping fails.

For example, when you use Select * from uid.NEWID_TEST2;, the mapping fails.

Use the following SQL query:

Select CONVERT(CHAR(36),ID) AS ID, TESTCOLUMN from uid.NEWID_TEST2;

- When you use a custom query for Time field and select Parquet as the staging file format, you must convert the Time data type to nchar data type. Else, the mapping fails.

For example, Select CONVERT(nchar(16),[TIME]) AS [TIME] FROM [AzureDW_DIatScale].[TimeNUUID];

- When you run a mapping to write float or real data types, the source and target data don't match in some cases. The values written to the target are approximate and not the same as in the source. To write the exact values, map real and float data types with numeric or decimal data types.
- To use the IS NULL and IS NOT NULL operators in a source filter or an uncached lookup, set the following property as a JVM option under the DTM type in the Secure Agent properties:

-DENABLE_NULL_FLAG_FOR_UNCACHED_LOOKUP=true

- When you define a Not Parameterized filter condition with IS NULL or IS NOT NULL operators, the mapping fails with the following error:

▪ Operation failed: Invalid expression string for filter condition

You must define an advanced filter condition to use IS NULL or IS NOT NULL operators to filter rows that contain null values.

- When you use pre-SQL and post-SQL queries in a Microsoft Azure Synapse SQL mapping and a runtime error is encountered, the mapping fails if you have configured the following property in the JVM options of the Secure Agent:

▪ -DAzureSynapseFailOnPrePostSQLError=true

If you don't configure this property, the mapping does not display an error and runs successfully.

- Use only non-UTF8 collation when you create a database in Microsoft Azure Synapse SQL.

Source transformation

- You cannot preview data for binary or varbinary columns for Microsoft Azure Synapse SQL objects in a mapping.
- When read a JSON file from a Microsoft Azure Data Lake Storage Gen2 source and write to a Microsoft Azure Synapse SQL target created at runtime, ensure that the source data does not have null values.
- When you read data from multiple Microsoft Azure Synapse SQL sources, ensure that the table names for the source object and the related object are not the same, regardless of the name case.
- When you configure a join to read from multiple objects where one or more tables have the same column name, the mapping fails.
- When you read external tables, ensure that two or more tables are not in the same location in Microsoft Azure Blob Storage or Microsoft Azure Data Lake Storage Gen2.
- When you read data from a source that contains varbinary columns, the Secure Agent reads the empty values from varbinary columns as NULL.
- Custom query as source

▪ When you use the query source type in a mapping to read from multiple tables, and you configure a join for one or more tables that have the same column names, the mapping fails.

For example, see the following SQL query that involves a full outer join between two tables EMPLOYEE and DEPARTMENT that are part of the SALES.PUBLIC schema, where two columns have the same name, CITY:

SELECT EMP_ID, NAME, CITY, DEPT_ID, DEPT_NAME, CITY

FROM SALES.PUBLIC.EMPLOYEE

FULL OUTER JOIN SALES.PUBLIC.DEPARTMENT ON EMP_ID = DEPT_ID

To distinguish the conflicting column names, add aliases that the database can refer to while joining the tables:

SELECT e.EMP_ID, e.NAME, e.CITY as ecity, d.DEPT_ID, d.DEPT_NAME, d.CITY as dcity

FROM SALES.PUBLIC.EMPLOYEE e

FULL OUTER JOIN SALES.PUBLIC.DEPARTMENT d ON e.EMP_ID = d.DEPT_ID

▪ If the query starts with the WITH clause, ensure that there is no semicolon (;) before WITH.

For example, ;With TEMP_OBJ AS (Select 'abc' AS FIELD1,123 AS Field2) Select Field1,Field2 from Temp_OBJ is not a valid query.

▪ Do not use a DECLARE statement in a source custom query.
▪ The parameters in the query must have a default value that can be overridden at runtime.
▪ When you use a custom query to read from multiple tables that have similar column names, you must use aliases for such column names while joining the tables.
▪ When you read data from Microsoft Azure Synapse SQL, you must configure the following property in the JVM options of the Secure Agent:

-DAzureSynapseDisableStagingForSort=true

▪ When you write data to Microsoft Azure Synapse SQL, you cannot use the ORDER BY clause.
▪ If you use a custom query in a Lookup transformation, you must perform the following tasks:

▪ Configure the -DAzureSynapseDisableStagingForSort=true property in the JVM options of the Secure Agent.
▪ 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.

Target transformation

- Behavior of target operations

Operation property	Treat Source Rows As property	Behavior
Insert, delete, or update	None or data driven	Value of the Operation property is given precedence.
Upsert	None or data driven	Update operation is performed. In this case, you must set both the Operation property and the Treat Source Rows As property to Upsert to perform an upsert operation.
Insert, delete, update, or upsert	Insert, delete, update, or upsert	Value of Treat Source Rows As property is given precedence.

- When you use the copy command, adhere to the following rules:

▪ When you stage files in Microsoft Azure Data Lake Gen2, you can use the copy command only with Service Principal Authentication.
▪ You cannot completely parameterize a multi-line copy command using a parameter file.

- When you create a Microsoft Azure Synapse SQL target at run time, ensure that the field names in the source do not contain special characters.
- When you create a target at runtime, the fields of the Uniqueidentifier data type in the source are converted to Nvarchar data type in the target.

To retain the Uniqueidentifier data type, edit the metadata for the target fields and change the native type of the fields to Uniqueidentifier.

- When you write data to a Microsoft Azure Synapse SQL target and if the source table has columns of the Datetime or Datetime2 data type, the seconds value is rounded to the nearest value.
- When you write data to Microsoft Azure Synapse SQL and override the default update SQL statement, ensure that the parameter query does not exceed 600 characters.
- When you upsert or update data to a Microsoft Azure Synapse SQL target and multiple rows in the source table contain the same value for the column on which the primary key is defined, the first row with the value is correctly updated but the subsequent rows with the same value are incorrectly updated in the target.
- When you use an ODBC connection to connect to Microsoft Azure Synapse SQL and the target table has an IDENTITY column, you cannot update data in the target.
- When you run a Microsoft Azure Synapse SQL mapping that reads data with multiple non-null columns and all the non-null columns in the source are not mapped in the target, the mapping fails with the following error:

[FATAL] Exception: com.informatica.adapter.azuredw.metadata.adapter.InfaSynapseException: Non-null fields are not projected

Ensure that all the non-null columns in the source are mapped in the target.

- When you use the Snappy compression format to write data to Microsoft Azure Synapse SQL, the mapping retains a snappy-1.1.8****-libsnappyjava.so file in the temp directory on the agent machine after it runs successfully.
- When the data driven condition contains only the DD_REJECT operation, the mapping runs without generating any query.
- When you create a target at runtime and the real data type in the source object is mapped to float data type in the target, certain junk characters are appended to the values in the target.

For example, 9.999999E19 is written as 9.999999320799471E19 in the target.

Lookup transformation

- When you use an uncached lookup, you cannot look up data with unicode characters.
- When you specify a SQL override to override the default lookup query in a Microsoft Azure Synapse SQL source, ensure that you select Lookup Caching Enabled in the advanced properties of the lookup object.
- You can't use a lookup object as a query for uncached lookups.
- Guidelines for dynamic lookup cache

▪ The Lookup transformation must be a connected transformation.
▪ You can specify only an equal operator in a lookup condition.
▪ You cannot use dynamic lookup cache in SQL ELT optimization.