Consider the following rules and guidelines for Microsoft Fabric Data Warehouse mappings:
General guidelines
- Ensure that the query you specify for the pre and post SQL statements are valid. Otherwise, the mapping fails.
- When you read from Microsoft Azure Synapse SQL and write to Microsoft Fabric Data Warehouse, the mapping fails.
- When you specify a custom query for a source object, ensure that the query does not contain a semicolon at the end of the SQL statement. Otherwise, the mapping fails.
- You cannot use input parameters in custom queries that read data from Microsoft Fabric Data Warehouse source objects.
- When you specify a single constant with the IIF condition in the data driven condition such as IIF(col_bigint>747654,DD_INSERT), the Secure Agent inserts the data into the target even for those rows that do not satisfy the condition.
- When you specify the DD_REJECT constant in the data driven condition, the Secure Agent does not log the rejected rows in the error file or the session log.
- When you configure a parameterized mapping with the Create New at Runtime option, enter the schema name/table name in the Object field in the Target transformation. The mapping validates the entry and throws a validation error if a table with the same name exists. However, if you enter only the table name and a table with the same name exists, the mapping does not validate the entry and inserts data to the existing table.
- A mapping that uses a MINUS operator in a custom query to read data from Microsoft Fabric Data Warehouse source objects fails at runtime. Use an EXCEPT operator instead of a MINUS operator.
- When you use an ORDER BY clause along with Join or Union clauses in a custom query to read data from Microsoft Fabric Data Warehouse source objects, ensure that you add "offset 0 rows" at the end of the query. Otherwise, the mapping fails.
- When you use a custom query in the Source transformation to call a stored procedure, the mapping fails at runtime.
- To avoid a Java heap size error when you write to Microsoft Fabric Data Warehouse, you need to allocate more memory to the DTM in the Secure Agent properties based on the amount of data that you want to write. Increase the -Xms and -Xmx values for the DTM in the JVM options in the system configuration details of the Secure Agent. The recommended -Xms value is 512 MB, while the recommended -Xmx value is 1024 MB.
- When you write to Microsoft Fabric Data Warehouse, the mapping fails if the table name or schema name contains unsupported special characters. However, the table name or schema name can contain the following special characters:
▪ !,@,#,$,%,^,&,_,|,:,",?,>,<
- When you filter records in a read operation, you can use only the following filter operators:
▪ Equals
▪ Greater
▪ Greater_or_equals
▪ Less_or_equals
▪ Less
▪ Not_Equals
Data types
- When you write data to a Microsoft Fabric Data Warehouse target created at runtime, Float or Real data types are mapped to and written as Double data type in the target. To write Float or Real data types, use the Edit Metadata option to edit the data type in the Target transformation.
- When you read data of the Float or Real data type from a source and write to a target, the mapping might not accurately write the Float or Real data type to the target.
For more information about writing the Float or Real data type to Microsoft Fabric Data Warehouse, see Float and Real (Transact-SQL) in the Microsoft documentation.
Parameterization
- When you use input parameters, specify the parameter name in the following format in a mapping task: $name$.
- You can't parameterize a target created at runtime using the parameter file.
- You can't use input parameters in a parameter file to parameterize the source filter and advanced properties at runtime.
- Ensure that you create the parameter before you override it using a parameter file.
- You can't parameterize only the connection. You can either parameterize both the connection and the object or only the object.
- When you parameterize the object, ensure that the metadata of the imported object matches the metadata fetched in the override.
- When you parameterize the connection or object, ensure that the connection or object that you want to override exists in Microsoft Fabric Data Warehouse.
- When you parameterize the connection and objects using the parameter file, ensure that the object name in the parameter file does not contain the schema name. If the connection in the parameter file doesn't contain the schema name, the mapping considers the schema name configured in the mapping task. To override the schema name, use the schema override property in the advanced properties.