ADF Pipeline failing with an error saying column can be found

Jiby Jacob 65

I have a pipeline that looks up a query to define the list of tables from source and usin for loop it should load data to Target Synapse pool but it is failing with following error on copy command.

ErrorCode=UserErrorSqlDWCopyCommandError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'id' not found in SQL DW table Schema.[Table]',Source=Microsoft.DataTransfer.ClientLibrary,'

Column ID is present in source and target. I have another pipeline with similiar logic but it does work. ( different ADF and ADLS account and different source and source tables)

(Changed Schema name and table)

phemanth 10,250 Reputation points Microsoft Vendor

2024-09-13T15:22:28.2066667+00:00
@Jiby Jacob

Thanks for reaching out to Microsoft Q&A.

Here are a few more things you might consider:

Case Sensitivity: Since the target DDL is in uppercase, ensure that your pipeline explicitly handles this case sensitivity. You might need to adjust your queries or mappings to match the case exactly.

Permissions: Permissions can indeed be a tricky part. Verify that your ADF instance has the necessary permissions to create tables and perform operations in Synapse. You can check the role assignments in Synapse and ensure that the ADF managed identity has the required roles.

PolyBase Issues: For PolyBase, ensure that the master key is created in the Synapse database. You can create it using the following command: SQL
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your_password';
Make sure that the ADF has the necessary permissions to use PolyBase.

ADLS and Synapse Permissions: Double-check the permissions between ADLS and Synapse. Ensure that the Synapse workspace has the necessary access to the ADLS storage account. You can do this by assigning the Storage Blob Data Contributor role to the Synapse managed identity on the ADLS account.

Pipeline Configuration: Since the pipeline works when a single table is selected and mapping is defined, there might be an issue with how the loop or dynamic content is configured. Ensure that the dynamic content correctly references the table names and columns, respecting the case sensitivity.

Comparison with Working Pipeline: Since you have a similar pipeline that works, compare the configurations of both pipelines in detail. Look for any differences in the settings, permissions, or environment configurations that might be causing the issue.

Hope this helps. Do let us know if you any further queries.
phemanth 10,250 Reputation points Microsoft Vendor

2024-09-16T12:10:07.9633333+00:00

@Jiby Jacob 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.
Jiby Jacob 65 Reputation points

2024-09-16T12:21:09.9966667+00:00

@phemanth Thanks for reaching out. I am still waiting to apply these changes
phemanth 10,250 Reputation points Microsoft Vendor

2024-09-17T16:59:30.9933333+00:00

@Jiby Jacob 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.
phemanth 10,250 Reputation points Microsoft Vendor

2024-09-18T16:28:16.6266667+00:00

@Jiby Jacob 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.

1 answer

Amira Bedhiafi 23,486 Reputation points

2024-09-11T20:36:13.0233333+00:00

Synapse is case-sensitive for column names in some scenarios. Check if the casing of the id column in the source and target tables matches exactly.

Ensure that both the ADF lookup query and the copy activity are referring to the column with the correct casing.

If the schema name or the table structure in the target differs slightly (e.g., different schema or column definitions), this could cause the issue. Make sure the schema structure in both environments is identical.

Double-check the target schema, especially since this error mentions Schema.[Table]. Ensure the id column exists under the correct schema.

If you're using a parameterized pipeline, ensure that the table schema and name passed to the Copy Activity or ForEach loop are correct and match the actual destination schema in Synapse.

Verify that the pipeline is picking up the correct table list and columns from the lookup activity.

Sometimes a data type mismatch between the source and target tables can lead to similar errors. Ensure that the data types for the id column are compatible between the source and target.

If you're using a distributed table in Synapse, ensure that the id column is properly indexed and distributed across the nodes. Sometimes distributed tables can behave differently, especially if the distribution method (like hash distribution) isn't set up properly.

Let me know if you need any further guidance on a specific part!
Please sign in to rate this answer.
Jiby Jacob 65 Reputation points

2024-09-12T22:16:13.4966667+00:00

Thanks @Amira Bedhiafi source and target DDL match and the only difference is target DDL is in Upper case. I have tried staging the data in ADLS in parquet format and running into same issues. it works when a single table is selected and mapping is defined. However, i have a similar pipeline on a different ADF and ADLS instance and it works perfectly using copy command. I have also tried polybase but it failing on master key. I am assuming ADF instance does not have create table permission. Also, reached out our support team and they assume it could be a permission between ADLS and Synapse.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

ADF Pipeline failing with an error saying column can be found

1 answer

Your answer