Target_WriteMetadata - SQLTable

Hi Everyone,

I am currently trying to implement the CDMutil pipeline using the template in the below link. I will give a high level overview of the problem. If anyone does know what the issue is, please respond to this post.

On a high-level we have tables in dynamics 365, and using the export to data lake feature the tables are being exported to ADLS gen2. The data that comes to the ADLS is in cdm format. In our storage container, we have several tables, and there is a model.json that contains the metadata for those tables.

The idea is to use the cdm util template in the link below, and build a synapse pipeline and take the tables from adls, marry those with the model.json's metadata and populate those tables in Azure Synapse dedicated pool. Using the template, I have configrued the linked service for ADLS and Synapse Dedicated pool. I have configured the source and sink, and I am using the pre-copy script that comes with the template in the copymetadata stage. And in the target SQL table when it writes the tables from adls, there is another default sql script in that stage. The issue is, when i debug the pipeline, I encounter this error,

Operation on target CreateSQLTable failed: The SELECT statement is not allowed in user-defined functions.
--Metadata prep--

The above error is, essentially in the default target sql script in the write stage. There is a user defined function in the script, which has select statements in it. I am reaching out to the community here to know, if anyone that used this template for a similar usecase has come across this issue. Any input on this would be greatly appreciated.

https://github.com/microsoft/Dynamics-365-FastTrack-Implementation-Assets/blob/master/Analytics/CDMUtilSolution/readme_cdmutilpipeline.md

Start with readme_cdmutilpipeline.md and the default target SQL script used in the write stage, then trace the copymetadata and CreateSQLTable steps that produce the reported error. Reproduce the pipeline failure and determine the expected SQL behavior; done means the CDM metadata can create the target Synapse tables without the user-defined-function error.