How to change external datasource as per environment for Synapse Serverless SQL CI/CD pipeline via azure devops ?

Question

How to change external datasource as per environment for Synapse Serverless SQL CI/CD pipeline via azure devops ?

Siddharth Joshi 231

Hi, I'm deploying serverless sql objects like external tables, external resources, data source, views etc via azure devops dacpac deployment but my deployment is failing because my view is pointing two external tables and those tables point to Dev ADLS but as I'm deploying all objects to UAT and my UAT ADLS is different that's why my deployment is failing so I want to change ADLS endpoint to UAT before publishing to UAT but via dacpac. Please let me know how I can do this ?

Accepted answer

0 additional answers

Your answer

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

@Siddharth Joshi Thankyou for using Microsoft Q&A platform and thanks for posting your question here. I understand you want to override ADLS endpoints for higher environment while deploying serverless sql resources using dacpac. You can try using SQLCMD variables to change the ADLS endpoint before publishing to UAT via DACPAC.

In your SQL script, replace the ADLS endpoint with a SQLCMD variable. For example, replace https://devadls.blob.core.windows.net with $(adls_endpoint).
In your Azure DevOps pipeline, define a SQLCMD variable named adls_endpoint with the value of your UAT ADLS endpoint.
In your DACPAC deployment task, add the /v:adls_endpoint=$(adls_endpoint) argument to the Additional SqlPackage.exe arguments field.

You can find more relevant information in below resources: Deploying a dacpac to a serverless SQL pool
sqlcmd - Use with scripting variables Use SQLCMD Variables for User Name in DACPAC

Hope it helps. Kindly accept the answer if it helped. Thankyou

Siddharth Joshi 231 Reputation points

2023-04-11T09:21:45.7733333+00:00
Hello @AnnuKumari-MSFT As I'm using dacpac deployment so directly it connects to Dev Serverless SQL Pool extract the objects first and then published so in this case we are not using sql scripts and we are not using visual studio or any other IDE as we are directly fetching DB objects via Synapse workspace SQL pool. Latest Update - I have tried with Azure data studio where I created new DB project in which I have external tables (pointing DELTA File format) , resources (DELTA FILE FORMAT), view etc but when I tried to build the project to create dacpac but its giving me error : It seems even Azure data studio latest version is not supporting DELTA File format

Build error SQL71501: SqlExternalTable: [dbo].[xxxx] has an unresolved reference to SqlExternalFileFormat [DeltaLakeFormat]. Build error SQL46010: Incorrect syntax near DELTA.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-05-19T05:46:59.0666667+00:00

Hi Siddharth Joshi ,

As mentioned by you, It seems that Azure Data Studio does not support Delta file format yet. You can try using Visual Studio or another IDE that supports Delta file format to build the project and create the dacpac.

Kindly check if these resources helps:

create dacpac in visual studio

Getting started with Data-Tier applications in Visual Studio

If still you are facing issue, I would recommend you to create a support ticket with Microsoft so that our product team can directly engage with you and provide solution after deeper investigation. Here is how you can do that: Create an Azure support request

Kindly accept the answer if it helped by clicking on Accept answer button as accepted answer helps community.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-05-24T06:40:54.0066667+00:00

Hi Siddharth Joshi ,

Just checking if the above response helped.

In case it was helpful, kindly accept the answer by clicking on Accept answer button , if not , then I would recommend you to create a support ticket with Microsoft so that our product team can directly engage with you and provide solution after deeper investigation.

Here is how you can do that: Create an Azure support request

Thankyou
BGO 46 Reputation points

2023-08-17T18:54:31.25+00:00

@AnnuKumari-MSFT In #1 when you say: "In your SQL script, replace the ADLS endpoint with a SQLCMD variable..."
What SQL script do you mean?

Share via

How to change external datasource as per environment for Synapse Serverless SQL CI/CD pipeline via azure devops ?

0 additional answers

Your answer