Issue with Leading Spaces in Key Columns Using ADF SAP CDC Connector

Question

Issue with Leading Spaces in Key Columns Using ADF SAP CDC Connector

Arvind Ramalingam 0

Problem Statement:

I'm utilizing Azure Data Factory (ADF) with the SAP CDC connector to extract data from several tables in SAP ECC and SAP HANA. I encountered a significant issue regarding leading spaces in key columns during data extraction.

Here’s a detailed description of the problem:

Scenario:

When extracting data, the source system contains values for the MATNR field as "ABC" and "ABC ". The first value ("ABC") doesn't have a leading space, while the second value ("ABC ") has a leading space.

Issue:

During the data extraction process via ADF mapping dataflows and the SAP CDC connector, the data is written to a staging text file. However, the leading spaces in the field values are trimmed automatically by the connector or the data flows. As a result, both values are transformed to "ABC" in the staging file.

Consequences:

Duplication Issues: When the affected field is a key column, this eradicates the ability to distinguish between the records, leading to duplicates and incorrect data representation.

Example:

Source System Data:

Record 1: MATNR = "ABC"

Record 2: MATNR = "ABC "

Extracted Data in Staging File:

Record 1: MATNR = "ABC"

Record 2: MATNR = "ABC"

Request:

I'm seeking help or suggestions on how to configure the ADF SAP CDC connector or mapping dataflows to preserve leading spaces during the extraction process. If there are any techniques or best practices to handle this scenario effectively, please share them.

Things I've Tried:

Checking configuration options in the SAP CDC connector.

Using transformations in ADF mapping dataflows.

Unfortunately, none of these approaches seem to prevent the automatic trimming of leading spaces.

Additional Information:

Tools Used: Azure Data Factory, SAP CDC Connector, Mapping Dataflows

Source Systems: SAP ECC, SAP HANA

Target File Type: Text File in staging and parquet in sink

Any guidance or solutions to maintain the integrity of key columns during extraction would be greatly appreciated.

Smaran Thoomu 22,760 Reputation points Microsoft External Staff

2025-04-11T13:33:22.0133333+00:00
Hi @Arvind Ramalingam
What you’re encountering is a known challenge with some connectors and data handling in ADF. Specifically, the SAP CDC connector and Mapping Data Flows can sometimes auto-trim leading/trailing spaces, especially when writing to text-based staging formats. This happens because of default behaviors in string handling, either at the connector level or during serialization to text files.

Possible Approaches to Try:

Check Data Flow Sink Settings: In your mapping data flow, verify if there are any implicit trimming functions applied at the sink transformation stage, especially while writing to the text file. Ensure any schema mappings preserve data types as string and avoid auto-trim.

Use Derived Column Transformation (Pad Workaround): As a workaround, consider padding your key fields with a visible placeholder (like ~) temporarily before writing to text, then revert this downstream: Example: iif(startsWith(MATNR, " "), "~" + MATNR, MATNR)

Switch to Binary Format for Staging: Text file formats often have issues preserving exact formatting. Consider staging to a binary-friendly format (like Avro or Parquet) first, which maintains whitespace integrity better, and only convert to text if necessary at the final stage.

Connector-Level Limitations: Based on the connector's current design, if none of the above work, this might be an inherent limitation. If so, I recommend raising a support ticket to Microsoft for deeper analysis or a potential feature request.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Arvind Ramalingam 0 Reputation points

2025-04-11T14:40:27.85+00:00

@Smaran Thoomu Thank you for your response. In ADF mapping dataflows with SAP CDC as Source, we have provision to setup staging folder from ADLS and its internal behavior of ADF SAP CDC connector to bring it as text files to the staging folder. I like to have staging folder in binary format like AVRO or PARQUET but in this case we don't have it as an option. If staging data comes with leading or trailing space, then we can handle it in expressions in derived columns but staging itself was auto-trimmed (leading or trailing) spaces. I am wondering if there are any json configuration to be added to SAP Source settings in ADF like enablemultiLine : true to handle this scenario.
Smaran Thoomu 22,760 Reputation points Microsoft External Staff

2025-04-14T10:25:08.2866667+00:00

@Arvind Ramalingam As you have rightly observed, in the current behavior of the ADF SAP CDC connector, the staging step is internally managed and defaults to text file formats, where leading and trailing spaces in string fields (even key fields like MATNR) are unfortunately auto-trimmed.

Regarding your question about JSON configurations at the source level:
At this point, the SAP CDC connector does not expose advanced JSON properties like enableMultiLine or similar for handling whitespace preservation during initial extraction. The staging folder format and serialization approach are governed by the connector's internal logic and are not configurable to use binary formats like Avro/Parquet at the staging level (only post-staging transformations can utilize those formats).

Although not ideal, one approach is to immediately post-process the staging text files using a secondary dataflow that compares original source values (if you have access to parallel extract) and "reintroduces" the spaces before further downstream processing.

Since this trimming happens at staging, it would be good to identify and log affected keys early to avoid duplication or loss downstream.

At this time, there is no JSON configuration at the source to preserve leading spaces before the staging step, but I agree this would be a valuable enhancement to the connector’s capabilities.

Could you please consider sharing your feedback through the official Azure Feedback channel or via a support ticket? This will help the engineering team assess the priority and explore improvements in future updates.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Arvind Ramalingam 0 Reputation points

2025-04-14T13:21:27.71+00:00

Thank you for your response. I will raise a support ticket with Microsoft.
Smaran Thoomu 22,760 Reputation points Microsoft External Staff

2025-04-14T13:55:03.9133333+00:00

@Arvind Ramalingam If you raise any support ticket, please let me know the SR # for tracking purposes, and we will provide updates on the issue you're facing.
Smaran Thoomu 22,760 Reputation points Microsoft External Staff

2025-04-15T11:15:40.9933333+00:00

@Arvind Ramalingam We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Arvind Ramalingam 0 Reputation points

2025-04-16T01:53:26.38+00:00

I don't have any resolution yet. I am working on to create SR#. I will share the SR# once it's created.
Smaran Thoomu 22,760 Reputation points Microsoft External Staff

2025-04-16T14:54:20.47+00:00

@Arvind Ramalingam Thanks for the update, and appreciate you continuing to explore this issue.

Please do share the Support Request (SR#) here once it's created, so we can help track the case and potentially escalate it internally if needed. This also helps others in the community who might be facing a similar challenge.

Looking forward to your update.

Your answer

Smaran Thoomu 22,760 Reputation points Microsoft External Staff

2025-04-11T13:33:22.0133333+00:00

Hi @Arvind Ramalingam
What you’re encountering is a known challenge with some connectors and data handling in ADF. Specifically, the SAP CDC connector and Mapping Data Flows can sometimes auto-trim leading/trailing spaces, especially when writing to text-based staging formats. This happens because of default behaviors in string handling, either at the connector level or during serialization to text files.

Possible Approaches to Try:

Check Data Flow Sink Settings: In your mapping data flow, verify if there are any implicit trimming functions applied at the sink transformation stage, especially while writing to the text file. Ensure any schema mappings preserve data types as string and avoid auto-trim.

Use Derived Column Transformation (Pad Workaround): As a workaround, consider padding your key fields with a visible placeholder (like ~) temporarily before writing to text, then revert this downstream: Example: iif(startsWith(MATNR, " "), "~" + MATNR, MATNR)

Switch to Binary Format for Staging: Text file formats often have issues preserving exact formatting. Consider staging to a binary-friendly format (like Avro or Parquet) first, which maintains whitespace integrity better, and only convert to text if necessary at the final stage.

Connector-Level Limitations: Based on the connector's current design, if none of the above work, this might be an inherent limitation. If so, I recommend raising a support ticket to Microsoft for deeper analysis or a potential feature request.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Arvind Ramalingam 0 Reputation points

2025-04-11T14:40:27.85+00:00

@Smaran Thoomu Thank you for your response. In ADF mapping dataflows with SAP CDC as Source, we have provision to setup staging folder from ADLS and its internal behavior of ADF SAP CDC connector to bring it as text files to the staging folder. I like to have staging folder in binary format like AVRO or PARQUET but in this case we don't have it as an option. If staging data comes with leading or trailing space, then we can handle it in expressions in derived columns but staging itself was auto-trimmed (leading or trailing) spaces. I am wondering if there are any json configuration to be added to SAP Source settings in ADF like enablemultiLine : true to handle this scenario.
Smaran Thoomu 22,760 Reputation points Microsoft External Staff

2025-04-14T10:25:08.2866667+00:00

@Arvind Ramalingam As you have rightly observed, in the current behavior of the ADF SAP CDC connector, the staging step is internally managed and defaults to text file formats, where leading and trailing spaces in string fields (even key fields like MATNR) are unfortunately auto-trimmed.

Regarding your question about JSON configurations at the source level:
At this point, the SAP CDC connector does not expose advanced JSON properties like enableMultiLine or similar for handling whitespace preservation during initial extraction. The staging folder format and serialization approach are governed by the connector's internal logic and are not configurable to use binary formats like Avro/Parquet at the staging level (only post-staging transformations can utilize those formats).

Although not ideal, one approach is to immediately post-process the staging text files using a secondary dataflow that compares original source values (if you have access to parallel extract) and "reintroduces" the spaces before further downstream processing.

Since this trimming happens at staging, it would be good to identify and log affected keys early to avoid duplication or loss downstream.

At this time, there is no JSON configuration at the source to preserve leading spaces before the staging step, but I agree this would be a valuable enhancement to the connector’s capabilities.

Could you please consider sharing your feedback through the official Azure Feedback channel or via a support ticket? This will help the engineering team assess the priority and explore improvements in future updates.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Arvind Ramalingam 0 Reputation points

2025-04-14T13:21:27.71+00:00

Thank you for your response. I will raise a support ticket with Microsoft.
Smaran Thoomu 22,760 Reputation points Microsoft External Staff

2025-04-14T13:55:03.9133333+00:00

@Arvind Ramalingam If you raise any support ticket, please let me know the SR # for tracking purposes, and we will provide updates on the issue you're facing.
Smaran Thoomu 22,760 Reputation points Microsoft External Staff

2025-04-15T11:15:40.9933333+00:00

@Arvind Ramalingam We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Arvind Ramalingam 0 Reputation points

2025-04-16T01:53:26.38+00:00

I don't have any resolution yet. I am working on to create SR#. I will share the SR# once it's created.
Smaran Thoomu 22,760 Reputation points Microsoft External Staff

2025-04-16T14:54:20.47+00:00

@Arvind Ramalingam Thanks for the update, and appreciate you continuing to explore this issue.

Please do share the Support Request (SR#) here once it's created, so we can help track the case and potentially escalate it internally if needed. This also helps others in the community who might be facing a similar challenge.

Looking forward to your update.

Share via

Issue with Leading Spaces in Key Columns Using ADF SAP CDC Connector

Your answer