0

I am trying to copy data from Synapse and load into Snowflake, for this i am using Azure Data Factory and control table having source and target fields names

My problem here is the snowflake schema name starts with number for example 9289RESIST.Tablename, but this is failing in ADF due to schema name start with number. How to give the sink table schema name in Azure Copy activity?

I tried adding double cotes for schema name "9289RESIST" but it was returning me errors.

James Z
  • 12,209
  • 10
  • 24
  • 44
user15488631
  • 85
  • 2
  • 10
  • What errors did it give you when you tried the double-quotes? When using double-quotes, make sure that you are using the same case that was used when the schema was created in Snowflake. – Mike Walton Feb 05 '23 at 17:50
  • Is it not possible to select the table from dropdown in the sink dataset? – Aswin Feb 06 '23 at 03:43
  • from my control table i just gave schema name without double quotes ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [42000] SQL compilation error: syntax error line 1 at position 26 unexpected '928'.s – user15488631 Feb 06 '23 at 13:57
  • I tried adding double cotes for schema name "9289RESIST" -- **Did you add double quotes by changing the data in control table itself?** – Aswin Feb 07 '23 at 06:37

1 Answers1

0

I created a schema with name 923_rlschema in snowflake and tried to call it dynamically from ADF by wrapping the schema name within double quotes and got the same error.

Message": "ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [42000] SQL compilation error:\nsyntax error line 1 at position 26 unexpected '923'.\nsyntax error line 1 at position 26 unexpected '923'.\nsyntax error line 1 at position 38 unexpected '""'.,

  • Then I removed the double quotes and control table looks as in below image.

enter image description here

  • This control table is taken as a dataset in lookup activity enter image description here

  • For-each activity is taken and Lookup activity array output is given as items in for-each activity. @activity('Lookup1').output.value

  • Inside for-each activity, copy activity is taken and source dataset is given

  • In sink dataset, schema name and table name are given as a dynamic content.

enter image description here

When pipeline is run, it got executed successfully.

Aswin
  • 4,090
  • 2
  • 4
  • 16