Syntax error in the metadata-driven control table script generated by the copy data wizard.

Question

ADF ControlTable Script.txt

In ADF I have selected the Metadata-driven Copy Task in the Ingestion wizard.

My source database is Azure SQL (WorldWideImporters), and my destination database is also Azure SQL (AdventureWorks2014). I'm using the destination database to hold my control table. I run through the wizard and select my tables to be copied (only 2 tables - both doing a full load). At the end of the routine my ADF pipelines and datasets are successfully created and the SQL script to create and populate the control table is also generated (attached). However, the SQL script will not execute as it returns a syntax error saying "Msg 156, Level 15, State 1, Line 675 Incorrect syntax near the keyword 'AS'.". Is this an error in the ADF script generation?

Answer 1

@Warren Briggs Welcome to Microsoft Q&A platform and thanks for posting your question.

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

**Ask:**In ADF I have selected the Metadata-driven Copy Task in the Ingestion wizard.

My source database is Azure SQL (WorldWideImporters), and my destination database is also Azure SQL (AdventureWorks2014). I'm using the destination database to hold my control table. I run through the wizard and select my tables to be copied (only 2 tables - both doing a full load). At the end of the routine my ADF pipelines and datasets are successfully created and the SQL script to create and populate the control table is also generated (attached). However, the SQL script will not execute as it returns a syntax error saying "Msg 156, Level 15, State 1, Line 675 Incorrect syntax near the keyword 'AS'.". Is this an error in the ADF script generation?

Solution: I was able to identify the problem. To run the OPENJSON command from the generated script the SQL database compatibility level must be 130 or higher. The database for my control table was only at 120. Once I increase the compatibility level for the database, the generated script ran without error.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

I was able to identify the problem. To run the OPENJSON command from the generated script the SQL database compatibility level must be 130 or higher. The database for my control table was only at 120. Once I increase the compatibility level for the database, the generated script ran without error.