When you use Rank transformation, the data is not stored as per the Rankindex order in the database.
When you read varchar or nvarchar fields and use the Rank transfomation, the target data is sorted as per collation of the database.
When you use Rank transformation, the session sort order that you specify in the advanced session property is not considered in a
Microsoft Azure Synapse SQL
mapping.
When you select multiple objects as the source type, you cannot read more that two objects.
When you use a custom query as the source type, the first three letters of the table name alias is always
INF
.
To write date/time fields to a Microsoft Azure Synapse SQL target created at the runtime, edit the metadata for the target fields and change the native data type of all the date/time fields to datetime2. Else, the mapping fails.
When you use a custom query as the source type, ensure that the table name or column name that you specify in the query does not contain unicode characters. Else, the mapping fails.
When you use a Filter transformation, ensure that the filter value does not contain special characters. Else, the mapping does not write any rows to the target.
You can use the update and delete operations only for an existing target.
When you enable the
Create New at Runtime
option in a
Microsoft Azure Synapse SQL
target to write data from file such as Delimited, ORC, or Parquet, delete the
FileName
field.
The reject threshold for a
Microsoft Azure Synapse SQL
target is considered for the whole data set, not for a batch of data.
Do not use the Aggregator transformation with update operation.
Do not configure a nested aggregate function in an Aggregator transformation.
Do not configure parallel transformations in a mapping.
Ensure that the Expression transformation does not contain a variable field.
Use the ISNULL function within the IIF clause in Aggregator and Expression transformations.
Use the IN function in a Filter transformation or within the IIF clause in Aggregator and Expression transformations.
When you use STDDEV, VARIANCE, and TRUNC functions in an Aggregator transformation, data corruption is possible if the table has a defined decimal column with precision more than 28, but the table contains data with precision less than 28.
When you use the
variable in an Expression transformation in full
SQL ELT optimization
, you must use the
yyyy-mm-dd hh:mi:ss
date format.
For example,
to_char($lastruntime,'yyyy-mm-dd hh:mi:ss')
You cannot use the
ISNULL(column name1)<>ISNULL(column name2)
syntax.
Use the following syntax for Filter and Router transformations:
(ISNULL(column name1) AND ISNULL(column name2)) OR ( NOT ISNULL(column name1) AND NOT ISNULL(column name2))
Use the following syntax for Expression and Aggregator transformations:
IIF((NOT ISNULL(column name1) AND ISNULL(column name 2)) OR (ISNULL(column name1) AND NOT ISNULL(column name 2)), 1, 0)
When you use a Filter or Router transformation and specify a string value in the filter condition, the mapping appends the character N before the condition.
Select*from [Azure].[employee] where (10125 <= "INF1"."EMPLOYEE_ID_NEW") AND ("INF1"."JOB_ID_NEW" =
N
'FI_ACCOUNT');
When you pass columns with Null values in a Normalizer transformation, Null values are not written to the target.
When you push the TO_DATE() function to process in Microsoft Azure Synapse SQL, ensure that the value matches the supported format. Prefix 0 if the value contains a single digit.
When you push the TO_DATE() function to the Microsoft Azure Synapse SQL database and the argument contains null data, the mapping runs without
SQL ELT optimization
.
When you push the MD5() function to process in Microsoft Azure Synapse SQL, you must specify a value of the String data type. Else, the mapping runs without
SQL ELT optimization
.
When you push the MD5() function to process in Microsoft Azure Synapse SQL, the function returns different values for non-English characters in a mapping and in a mapping enabled with
SQL ELT optimization
.
You cannot push the MD5() function to Microsoft Azure Synapse SQL if the table or column names contain unicode or special characters.
When you push the REG_MATCH() function to process in Microsoft Azure Synapse SQL, it is recommended that you specify all the characters instead of a range of characters in the regular expression in a case-sensitive database. For example, use
[ABCDEF]
instead of
[A-F
].
When you push the TO_BIGINT() function to convert Varchar data to Bigint in Microsoft Azure Synapse SQL, use the format TO_BIGINT (numeric expression [, 1]) for the function to return the correct values. To round values stored in the Varchar column, convert values to any numeric format, and then convert to Bigint.
When you run a mapping enabled with
SQL ELT optimization
and push functions to process in Microsoft Azure Synapse SQL, the function considers the collation rules that are set in the database. This causes the functions to return different values in a mapping compared to a mapping enabled with
SQL ELT optimization
.
When you read from a Microsoft Azure Synapse SQL source and write to multiple targets from a mapping enabled for SQL ELT optimization in a single pipeline, and the Source transformation contains pre-SQL and post-SQL queries, the mapping runs these queries for each of the targets. This might cause data discrepancy. To avoid this, define the pre-SQL and post-SQL queries only in the Target transformation.
