CICD Synapse SqlPoolStoredProcedure Activity sqlPool Reference

Arthur Steijn 56

Hi,

Any help on how to CICD a SqlPoolStoredProcedure Activity? In the pipeline definition there is an object:

"sqlPool": {  
		"referenceName": "sqlPool",  
		"type": "SqlPoolReference"  
}

But in the TemplateForWorkspace.json that get's created this object is not there anymore. See below comparison, left side is the created template, right side the pipeline definition:

This means that after deployment, manually the reference to the sqlPool has to be set!?

Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-10-04T20:04:26.55+00:00
Hello @Arthur Steijn ,

Welcome to the MS Q&A platform.

From my end, I was able to see the dedicated pool object name on the 'Templateforworkspace.json' file. So during my pipeline release, demodev is referenced to the other environments. If 'devdemo' pool is not present in my other environment the release will fail.

"sqlPool": { "referenceName": "demodev", "type": "SqlPoolReference" },

and your understanding is correct. If you don't have the SQL pool object in the templateforworkspace.json file, then you need to manually refer to the SQLpool after the deployment.

------------------------------

If this answers your question, please consider accepting the answer by hitting the Accept answer button, as it helps the community.
Arthur Steijn 56 Reputation points

2022-10-05T09:39:47.747+00:00

Hey,

thanks for your reply.

How did you create your TemplateForWorkspace.json?

I didn't mention, but I am using the Azure DevOps task called: AzureSynapseWorkspace.synapsecicd-deploy.synapse-deploy.Synapse workspace deployment@2
and the 'validate' operation.
Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-10-05T16:29:06.56+00:00

Hello @Arthur Steijn ,
When you connect your synapse/ADF live environment to the Git repository by default, Templateforworkspace.json and Templateparamtersforworkspace.json files will be created under the workspace_publish branch.
Your Azure DevOps task(synapse workspace deployment task) will use these files to deploy the changes to the new environments.
I hope this clarifies your question. Please let me know if you have any further questions.

------------------------------

Please don't forget to Accept the Answer if the response helped
Arthur Steijn 56 Reputation points

2022-10-05T16:36:01.927+00:00

Thanks, for the reply.

The documentation makes me think the publish branch would not be needed, looking at the 'validate and deploy' option:
https://learn.microsoft.com/en-us/azure/synapse-analytics/cicd/continuous-integration-delivery#configure-the-deployment-task

where could I request such an improvement/feature for the sqlPool reference object?

thanks for your help
Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-10-06T18:55:26.413+00:00

Hello @Arthur Steijn ,
When you choose 'validate and deploy,' you need to provide the artifact root folder. The root folder has 'Templateforworkspace.json' and 'Templateparametersforworkspace.json'
using these files, the artifacts are deployed to the other environments.
Since the SQLpool name is not a secured string, it will be parameterized by default. From my end, I see the SQLpool name in the templateforworkspace.json file.
Arthur Steijn 56 Reputation points

2022-10-07T14:00:57.323+00:00

Hey,

In my opinion this is a bug.

Validate and deploy can be used to directly deploy the workspace from non-publish branch with the artifact root folder.

This root folder doesn't contain 'Templateforworkspace.json' and 'Templateparametersforworkspace.json' but does deploy all artifacts, except for this sqlPool object.
Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2022-10-07T17:50:01.113+00:00

Hello @Arthur Steijn ,
Thank you for the details.

We have a dedicated Azure DevOps support channel for DevOps issues. I would request you to post your query in this DevOps channel so that someone from the dedicated DevOps team can assist you on this.
Jared Wilkin 106 Reputation points

2022-10-27T18:24:52.163+00:00

@Arthur Steijn did you ever solve this? I am having the exact same problem, namely when performing the "validate" action in a CI/CD pipeline the resultant JSON files are missing the "sqlPool" setting as described caused any deployment using those files to be broken...
Arthur Steijn 56 Reputation points

2022-10-27T18:36:10.137+00:00

Well, solved it in the worst way possible. I reverted back to version 1 of the task.

Also reported a bug here https://developercommunity.visualstudio.com/t/Synapse-workspace-deployment2-SqlPoolSt/10183642
Jared Wilkin 106 Reputation points

2022-10-27T21:06:38.607+00:00

I submitted a bug here: https://github.com/Azure/Synapse-workspace-deployment/issues/68

maybe one of us will get some traction, but at the moment, I will have to revert to having users "publish" from within the studio just so that proper template files will be created.
Arthur Steijn 56 Reputation points

2022-10-28T08:37:15.037+00:00

FYI, not the right channel!

Hi,
Glad to see you on Developer Community.

It’s an issue with the extension of Synapse workspace deployment and it’s not the Azure DevOps built-in feature, so we can do nothing about it.

Therefore, we recommend that you can add a review or add feedback in the Windows Feedback Hub by referring to Synapse workspace deployment - Visual Studio Marketplace to get professional support.

Thank you for sharing your feedback in Azure DevOps.
Best regards

1 answer

Siddharth Joshi 211 Reputation points

2023-02-17T12:34:02.01+00:00

Hi @Arthur Steijn

I was facing same problem so to resolve this problem I did workaround by using file transform task first before synapse deployment task and transform "TemplateForWorkspace.Json" by overriding variables key like this "resources.4.properties.sqlPool.referenceName" so this will change sqlPool reference name and then we used synapse deployment task with deploy operation.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

CICD Synapse SqlPoolStoredProcedure Activity sqlPool Reference

1 answer