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

Microsoft Azure Synapse SQL source properties

In a mapping, you can configure a Source transformation to represent a single Microsoft Azure Synapse SQL object. When you read data from Microsoft Azure Synapse SQL, you can configure key range partitioning to optimize the mapping performance at run time.
The following table describes the Microsoft Azure Synapse SQL properties that you can configure in a Source transformation:
Property
Description
Connection
Name of the source connection. Select a source connection or click New Parameter to define a new parameter for the source connection.
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 any of the following source object:
  • - Single Object
  • - Multiple Objects1
  • - Query
  • - Parameter
When you specify a custom SQL query as source type, the Secure Agent evaluates the properties in the following order to run a mapping:
  1. 1. SQL Override
  2. 2. Table Name Override
  3. 3. Custom SQL
You cannot specify Custom SQL as a parameter in elastic mappings.
You cannot parameterize a source query object at runtime in a mapping.
Object
Name of the source object.
You can use external tables as Microsoft Azure Synapse SQL sources in mappings and elastic mappings.
Parameter
Select an existing parameter for the source object or click New Parameter to define a new parameter for the source object. The Parameter property appears only if you select Parameter as the source type.
Sort
Sort is not supported for Microsoft Azure Synapse SQL Connector.
1Applies only to mappings.
The remaining properties are applicable for both mappings and elastic mappings.
The following table describes the Microsoft Azure Synapse SQL source advanced properties that you can configure in a Source 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.
1Applies only to mappings.
The remaining properties are applicable for both mappings and elastic mappings.

Adding multiple source objects

Perform the following steps to add multiple source objects in a mapping:
    1. Click New > Mappings.
    2. Select Mapping and click Create.
    3. In the Source Properties page, specify the name and provide a description in the General tab.
    The image shows the properties to be configured in the General tab.
    4. Click the Source tab.
    5. Select the source connection and source type as Multiple Objects to be used for the task.
    The image shows the source connection and source type for the task
    6. In the Objects and Relationships section, click the arrow to open the Action menu and then select Add Source Object.
    The image shows the Action menu from where you can add the source object
    7. Select a source object from the list and click OK.
    The image shows the list of available source objects.
    8. In the Objects and Relationships section, click the arrow next to the source object and then click Advanced Relationship to add related objects.
    The image shows the Advanced Relationship option to add related objects.
    9. In the Advanced Relationship page, click Add Object.
    The image shows the Advanced relationship page with the Add Object option.
    10. In the Select Source Object page, select an object from the list and click OK.
    The image shows the list of available objects
    11. In the Advanced Relationship page, select the required fields for the objects and create a join condition to define the relationship for all objects.
    The image shows the the join condition for the objects.
    Note: You can join only two source tables. You must create an inner join to define the relationship. You must define only a join condition, not the complete join query. If the table name or column name is a reserved keyword, enclose the name in square brackets. For example, [AUTHORIZATION].[BROWSE]=[int].[BROWSE].
    12. Click OK.

Key range partitioning

You can configure key range partitioning when you use a mapping task to read data from Microsoft Azure Synapse SQL sources. With key range partitioning, the Secure Agent distributes rows of source data based on the fields that you define as partition keys. The Secure Agent compares the field value to the range values for each partition and sends rows to the appropriate partitions.
Use key range partitioning for columns that have an even distribution of data values. Otherwise, the partitions might have unequal size. For example, a column might have 10 rows between key values 1 and 1000 and the column might have 999 rows between key values 1001 and 2000. If the mapping includes multiple sources, use the same number of key ranges for each source.
When you define key range partitioning for a column, the Secure Agent reads the rows that are within the specified partition range. For example, if you configure two partitions for a column with the ranges as 10 through 20 and 30 through 40, the Secure Agent does not read the rows 20 through 30 because these rows are not within the specified partition range.
You can configure a partition key for fields of the following data types:
You cannot use key range partitions in elastic mappings and when a mapping includes any of the following transformations:
Note: When you use multiple partitions, the start range for the first partition and the end range for the last partition are not included in the WHERE clause of the polybase command.
Custom queries and SQL override are not applicable when you use key range partitioning.

Configuring key range partitioning

Perform the following steps to configure key range partitioning for Microsoft Azure Synapse SQL sources:
    1. In the Source Properties, click the Partitions tab.
    2. Select the required partition key from the list.
    3. Click Add New Key Range to define the number of partitions and the key ranges based on which the Secure Agent must partition data.
    Use a blank value for the start range to indicate the minimum value. Use a blank value for the end range to indicate the maximum value.
    The following image displays the Partitions tab:
    The image shows the partitions and the corresponding key ranges.