You can configure pass-through partitioning to optimize the performance of mapping tasks when you read data from Microsoft SQL Server sources.
To configure pass-through partitioning, specify a query or filter value for each partition to read data in parallel.
Consider the following rules and guidelines when you run a mapping task enabled for pass-through partitioning to read from Microsoft SQL Server:
•When you upsert data in a Microsoft SQL Server target, data discrepancies occur.
•Even though you configure a filter in the Source transformation, you can't filter source data.
•When you specify an SQL override in a Source transformation, the SQL override statement overrides the SQL query and source filter statements for the configured partitions.
For example, if you specify an SQL query or source filter for five partitions, the SQL override overrides the SQL query and source filter statements for all the five partitions.
Processing Unicode characters
Before you enable pass-through partitioning in a mapping task when the SQL Query or Source Filter value of the configured partitions contain Unicode characters, you need to set certain system and custom properties for the Secure Agent in Administrator.
1Select Administrator > Runtime Environments.
2On the Runtime Environments page, select the Secure Agent that runs the mapping task.
3Click Edit.
4In the System Configuration Details section, perform the following steps:
aSelect Data Integration Server as the service and DTM as the type.
bEdit the OdbcDataDirectNonWapi system property and set the value to 0.
5In the Custom Configuration Details section, perform the following steps:
aSelect Data Integration Server as the service and DTM as the type.
bEnter AddQuotesAlways in the Name field and set the value to Yes.
