question

JohnnyHumphrey-3892 avatar image
0 Votes"
JohnnyHumphrey-3892 asked JohnnyHumphrey-3892 answered

Invalid column name - Data factory Copy activity Source SQL Server

  • I have a Pipeline in Synapse with (currently) just a Copy activity, which has a SQL Server Source and an ADSL Gen 2 Parquet file Sink.

  • It uses a large query with about 140 columns and 20 tables.

  • The activity works fine with no partitioning, if very slow.

  • I am trying the Dynamic Range Partition option.

  • As per the instructions, I added ?AdfDynamicRangePartitionCondition to the WHERE clause.

  • In the SELECT clause, the column appears as bi.INVOICE_NO 'Billing_Item_No'

  • In the FROM clause, the table is PRODUCTION.dbo.BILLING_INVOICE bi

  • For the Partition Column Name, I have tried the following, all of which cause the activity to fail with an Invalid column name message.
    - bi.INVOICE_NO (Message=Invalid column name 'bi.INVOICE_NO')
    - Billing_Item_No (Message=Invalid column name 'Billing_Item_No')
    - [Billing_Item_No] (Message=Invalid column name 'Billing_Item_No')
    - BILLING_ITEM_NO (Message=Invalid column name 'BILLING_ITEM_NO')
    - INVOICE_NO (Message=Ambiguous column name 'INVOICE_NO')
    - PRODUCTION.dbo.BILLING_INVOICE.INVOICE_NO (Message=Invalid column name 'PRODUCTION.dbo.BILLING_INVOICE.INVOICE_NO')

How do I reference the partition column?

Thanks,
Johnny



azure-data-factoryazure-synapse-analytics
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I went back and used the file created when I didn't use partitioning and the copy was successful. There was also no schema mapping in the Copy activity, all of that was handled by the defaults. I used a Spark pool to create a table from the Parquet file.
df = spark.read.load('abfss://<my_container>@<my_storage_acct>.dfs.core.windows.net/<my_parquet_file_path>;',
format = 'parquet')
df.write.mode("overwrite").saveAsTable("tests.billing_invoice")

and then use the serverless SQL to query the table:
SELECT MAX(Billing_Item_No) FROM dbo.billing_invoice;

That completes without error. So Billing_Item_No really should be the Partition column name.

1 Vote 1 ·

Hello @JohnnyHumphrey-3892 ,

Glad to know that your issue has resolved. You can accept it as answer. And thanks for sharing the solution, which might be beneficial to other community members reading this thread. Thank you.

0 Votes 0 ·

Sorry, I wasn't clear. That really wasn't an answer. I believe that my statement that Billing_Item_No really should be the Partition column name is true, but from my initial question, that was one of the many things that gave me an error when I tried that. This is the message that I received in that case:
Operation on target Billing Invoice DB6 failed: Failure happened on 'Sink' side. ErrorCode=UserErrorFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Upload file failed at path avalondata/Landing/BillingInvoice.,Source=mscorlib,''Type=System.Data.SqlClient.SqlException,Message=Invalid column name 'Billing_Item_No'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=207,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=207,State=1,Message=Invalid column name 'Billing_Item_No'.,},],'




0 Votes 0 ·
Show more comments

1 Answer

JohnnyHumphrey-3892 avatar image
0 Votes"
JohnnyHumphrey-3892 answered

I finally noticed that the error was coming from the Sink side.

Operation on target Billing Invoice DB6 failed: Failure happened on 'Sink' side. ErrorCode=UserErrorFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Upload file failed at path <my_data_path>.,Source=mscorlib,''Type=System.Data.SqlClient.SqlException,Message=Invalid column name 'bi.INVOICE_NO'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=207,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=207,State=1,Message=Invalid column name 'bi.INVOICE_NO'.,},],'


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.