Microsoft Azure Synapse SQL Connector > Mappings and mapping tasks with Microsoft Azure Synapse SQL > Microsoft Azure Synapse SQL lookup properties
  

Microsoft Azure Synapse SQL lookup properties

Use a Lookup transformation to retrieve data based on a specified lookup condition.
Use a Microsoft Azure Synapse SQL Lookup transformation to look up data in a Microsoft Azure Synapse SQL object. For example, the source table includes the customer code, but you want to include the customer name in the target table to make summary data easy to read.
You can use the Microsoft Azure Synapse SQL Lookup transformation to look up the customer name in another Microsoft Azure Synapse SQL object.
You can create the following lookups when you configure field mappings in a mapping task:
You cannot configure a connected or unconnected uncached lookup in an elastic mapping.
The following table describes the Microsoft Azure Synapse SQL lookup object properties that you can configure in a Lookup transformation:
Property
Description
Connection
Name of the lookup connection.
You can select an existing connection, create a new connection, or define parameter values for the lookup connection property.
If you want to overwrite the lookup connection properties at runtime, select the Allow parameter to be overridden at run time option.
When you switch between a non-parameterized and a parameterized Microsoft Azure Synapse SQL connection, the advanced property values are retained.
Source Type
Type of the source object.
Select one of the following options:
  • - Single Object
  • - Query
  • - Parameter
You cannot use custom query as the source type when you use a Lookup transformation in an elastic mapping.
You cannot use custom query as the source type in cached and dynamic cache lookups.
Lookup Object
Name of the lookup object for the mapping.
Parameter
A parameter file where you define values that you want to update without having to edit the task.
Select an existing parameter for the lookup object or click New Parameter to define a new parameter for the lookup object.
The Parameter property appears only if you select parameter as the source type.
If you want to overwrite the lookup connection properties at runtime, select the Allow parameter to be overridden at run time option.
When the task runs, the Secure Agent uses the parameters from the file that you specify in the advanced properties.
Define Query
If the source type is a query, displays the Edit Custom Query dialog box. Enter a valid custom query and click OK.
Multiple Matches
The behavior when the lookup condition returns multiple matches.
Select one of the following options:
  • - Return first row1
  • - Return last row1
  • - Return any row
  • - Return all rows
  • - Report error
Note: If the lookup table has an IDENTITY column, the data for the first row and last row is returned based on the sorting of the IDENTITY column. If the matched rows have duplicates then the values returned are random.
1Applies only to mappings.
The remaining properties are applicable for both mappings and elastic mappings.
You cannot use Binary, Varbinary, and Datetimeoffset datatypes columns as lookup conditions.
When you use a cached lookup with Microsoft Azure Synapse SQL connection and if the lookup condition contains a NULL value, the lookup condition is ignored.
The following table describes the Microsoft Azure Synapse SQL lookup advanced properties that you can configure in a Lookup transformation:
Property
Description
Azure Blob Container Name
Microsoft Azure Blob Storage container name. Required if you select Azure Blob storage in the connection properties.
ADLS FileSystem Name
The name of the file system in Microsoft Azure Data Lake Storage Gen2. Required if you select ADLS Gen2 storage in the connection properties.
You can also provide the path of the directory under given file system.
Schema Name Override
Overrides the schema specified in the connection.
Table Name Override
Overrides the table name of the imported Microsoft Azure Synapse SQL source table.
Field Delimiter
Character used to separate fields in the file. Default is 0x1e. You can specify 'TAB' or 0-256 single-char printable and non-printable ASCII characters. Non-printable characters must be specified in hexadecimal.
Note: Multi-char ASCII characters except TAB are not supported. You cannot use the following non-printable characters:
00x0, 0x0, 0x0A , 0x1B, 0x0D, and 0x1F
Number of Concurrent Connections to Blob Store1
Number of concurrent connections to extract data from the Microsoft Azure Blob Storage. When reading a large-size blob, you can spawn multiple threads to process data. Configure Blob Part Size to partition a large-size blob into smaller parts.
Default is 4. Maximum is 10.
Blob Part Size1
Partitions a blob into smaller parts each of specified part size. When reading a large-size blob, consider partitioning the blob into smaller parts and configure concurrent connections to spawn required number of threads to process data in parallel.
Default is 8 MB.
Pre-SQL
Pre-SQL command that must be run before reading data from the source.
Post-SQL
Post-SQL command that must be run after writing data to the target.
SQL Override
When you read data from a Microsoft Azure Synapse SQL object, you can configure SQL overrides and define constraints.
On Pre-Post SQL Error
Determines the behavior when a task that includes pre-SQL or post-SQL commands encounters errors. You can select any of the following options:
  • - Continue. The task continues regardless of errors.
  • - Stop. The task stops when errors occur while executing pre-SQL or post-SQL commands.
Quote Character
The Secure Agent skips the specified character when you read data from Microsoft Azure Synapse SQL.
Default is 0x1f.
Interim Directory1
Optional. Path to the staging directory in the Secure Agent machine.
Specify the staging directory where you want to stage the files when you read data from Microsoft Azure Synapse SQL. Ensure that the directory has sufficient space and you have write permissions to the directory.
Default staging directory is /tmp.
You cannot specify an interim directory for elastic mappings.
You cannot specify an interim directory when you use the Hosted Agent.
Tracing Level
Sets the amount of detail that appears in the log file. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
You cannot set the tracing level for elastic mappings.
Dynamic Lookup Cache1
Enable to keep the lookup cache synchronized with the target.
Pre-build Lookup Cache1
Allows the agent to build the lookup cache before the Lookup transformation receives the data. The agent can build multiple lookup cache files at the same time to improve performance.
Configure one of the following options:
  • - Auto. The agent decides the value at run time while building the cache.
  • - Value. The agent uses the value configured in the mapping.
1Applies only to mappings.
The remaining properties are applicable for both mappings and elastic mappings.
For more information about the Lookup transformation, see Transformations.

Dynamic lookup cache

You can configure a dynamic lookup cache to keep the lookup cache synchronized with the Microsoft Azure Synapse SQL target.
When you enable lookup caching, a mapping task builds the lookup cache when it processes the first lookup request.
If the cache is dynamic, the task updates the cache based on the actions in the task, so if the task uses the lookup multiple times, downstream transformations can use the updated data.
Based on the results of the lookup query, the row type, and the Lookup transformation properties, the mapping task performs one of the following actions on the dynamic lookup cache when it reads a row from the source:
Inserts the row into the cache
The mapping task inserts the row when the row is not in the cache. The mapping task flags the row as insert.
Updates the row in the cache
The mapping task updates the row when the row exists in the cache. The mapping task updates the row in the cache based on the input fields. The mapping task flags the row as an update row.
Makes no change to the cache
The mapping task makes no change when the row is in the cache and nothing changes. The mapping task flags the row as unchanged.
For information about dynamic lookup cache, see Transformations.

Rules and guidelines for dynamic lookup cache

Consider the following guidelines when you use a dynamic lookup cache: