You work for an organization that stores large amount of purchase order details, such as customer ID, item codes, and item quantity for all regions in Microsoft Azure Data Lake Storage Gen2. You need to port the data from Microsoft Azure Data Lake Storage Gen2 to Microsoft Azure Synapse SQL to quickly analyze the purchase order details and to increase future revenues.
Create a mapping in advanced mode to achieve faster performance when you read all the purchase records from Microsoft Azure Data Lake Storage Gen2 source and write the records to a Microsoft Azure Synapse SQL target.
1In Data Integration, click New > Mappings > Mapping.
2In the Mapping Designer, click Switch to Advanced.
The following image shows the Switch to Advanced button in the Mapping Designer:
3In the Switch to Advanced dialog box, click Switch to Advanced.
The Mapping Designer updates the mapping canvas to display the transformations and functions that are available in advanced mode.
4 Enter a name, location, and description for the mapping.
5On the Source transformation, specify a name and description in the general properties.
6On the Source tab, perform the following steps to provide the source details to read data from the Microsoft Azure Data Lake Storage Gen2 source:
aIn the Connection field, select the Microsoft Azure Data Lake Storage Gen2 source connection.
bIn the Source Type field, select the type of the source.
cIn the Object field, select the required object.
dIn the Advanced Properties section, provide the appropriate values.
7On the Fields tab, map the source fields to the target fields.
8On the Target transformation, specify a name and description in the general properties.
9On the Target tab, perform the following steps to provide the target details to write data to theMicrosoft Azure Synapse SQL target:
aIn the Connection field, select the Microsoft Azure Synapse SQL target connection.
bIn the Target Type field, select the type of the target.
cIn the Object field, select the required object.
dIn the Operation field, select the required operation.
eIn the Advanced Properties section, provide appropriate values for the advanced target properties.
10Map the flat file source and the Microsoft Azure Synapse SQL target.
11Click Save > Run to validate the mapping.
In Monitor, you can monitor the status of the logs after you run the task.
Rules and guidelines for mappings in advanced mode
Consider the following rules and guidelines when you create mappings in advanced mode:
•When you configure a Sequence transformation in a mapping in advanced mode, the mapping fails.
• When you create a connection and specify an incorrect value for an Azure storage-specific connection property, the test connection is successful. However, the mapping in advanced mode fails with the following exception: Caused by: org.apache.hadoop.fs.azure.AzureException: com.microsoft.azure.storage.StorageException: One of the request inputs is not valid
Ensure that you specify valid storage parameters when you configure the connection.
•When you select Microsoft Azure Data Lake Storage Gen2 as the storage type for both source and target connections, ensure that you use the same access key for both the source and target connections.
For example, if you use Key 1 as the access key for the source connection and Key 2 for the target connection, the mapping fails.
•You cannot use system assigned identity for mappings in advanced mode.
•You cannot add multiple pipelines in a mapping.
•When you specify the external data source in the connection properties, you must specify the container or file system used to create the external data source either in the connection properties or in the advanced properties.
•When you run a mapping in advanced mode and map the source fields of double or float data type to the target fields of string data type, the format of the values changes in the target.
The following table describes the change in the format of the values in the target:
Value in the source
Value in the target
1.7976931348623157e+308
1.79769313486232e+308
-9999999999999.99
-10000000000000
4.94065645841247e-324
4.9e-324
7956318123.99392483
7956318123.99392
•When you run a mapping in advanced mode to write float or real data types, the source data 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.
