CODEWITHSUNDEEP
snowflake-cloud-data-platformazure-data-factoryazure-synapsesnowflake-schema
user15488631
user15488631

Reputation: 95

snowflake schema with number how to use it in Azure data factory to copy data from Synapse to Snowflake

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.

Upvotes: 0

Views: 314

Answers (1)

Aswin
Aswin

Reputation: 7156

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.

Upvotes: 0

Related Questions