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.
You can select an existing connection, create a new connection, or define parameter values for the source connection property.
If you want to overwrite the parameter at runtime, select the Allow parameter to be overridden at run time option when you create a parameter. When the task runs, the agent uses the parameters from the file that you specify in the task advanced session properties.
You can switch between a non-parameterized and a parameterized Microsoft Azure Synapse SQL connection. When you switch between the connections, the advanced property values are retained.
Source Type
Type of the source object.
Select Single Object, Multiple Objects, Query, or 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:
1SQL Override
2Table Name Override
3Custom SQL
You cannot parameterize a source query object at runtime in a mapping.
You cannot specify Custom SQL as a parameter for mappings in advanced mode.
Object
The source object for the task.
You can use external tables as Microsoft Azure Synapse SQL sources in 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.
If you want to overwrite the parameter at runtime, select the Allow parameter to be overridden at run time option when you create a parameter. When the task runs, the agent uses the parameters from the file that you specify in the task advanced session properties. Ensure that the parameter file is in the correct format.
Filter
Filters records based on the filter condition.
You can specify a simple filter or an advanced filter.
Sort
Not applicable.
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
Required if you select Azure Blob storage in the connection properties.
The name of the container in Microsoft Azure Blob Storage.
The container name must not contain special characters.
ADLS FileSystem Name
Required if you select ADLS Gen2 storage in the connection properties.
The name of the file system in Microsoft Azure Data Lake Storage Gen2.
The file system name must not contain special characters.
You can also specify the path of the directory under the file system. Use only a forward slash to specify the directory path.
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.
Staging File Format
Type of file format to use when you stage the files in Azure storage.
Select one of the following formats:
- Delimited Text
- Parquet
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 applicable. 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 reading data from the source.
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.
Select one 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
Specifies the quote character to skip when you read data from Microsoft Azure Synapse SQL.
The quote character that you specify must not exist in the source table. If it exists, enter a different quote character value.
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 read permissions to the directory.
Default staging directory is /tmp.
You cannot specify an interim directory when you use the Hosted Agent.
Tracing Level1
Sets the amount of detail that appears in the log file. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
1Doesn't apply to mappings in advanced mode.
Adding multiple source objects
Perform the following steps to add multiple source objects in a mapping:
1Click New > Mappings.
2Select Mapping and click Create.
3In the Source Properties page, specify the name and provide a description in the General tab.
4Click the Source tab.
5Select the source connection and source type as Multiple Objects to be used for the task.
6In the Objects and Relationships section, click the arrow to open the Action menu and then select Add Source Object.
7Select a source object from the list and click OK.
8In the Objects and Relationships section, click the arrow next to the source object and then click Advanced Relationship to add related objects.
9In the Advanced Relationship page, click Add Object.
10In the Select Source Object page, select an object from the list and click OK.
11In the Advanced Relationship page, select the required fields for the objects and create a join condition to define the relationship for all 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 or the table name contains special characters, enclose the name in square brackets. For example, [AUTHORIZATION].[BROWSE]=[int].[BROWSE].
12Click 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:
•Any type of number data type. However, you cannot use decimals in key range values.
You cannot use key range partitions when you create a mapping in advanced mode and when a mapping includes any of the following transformations:
•Web Services
•XML to Relational
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:
1In the Source Properties, click the Partitions tab.
2Select the required partition key from the list.
3Click 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.
