Azure DevOps pipeline for SSDT project can not find the variables when it does not need to create object

Question

I am using Azure Devops to deploy my SSDT project. I am trying to update my Azure SQL Data Warehouse where I have DATABASE SCOPED CREDENTIAL and EXTERNAL DATA SOURCE.

I found this article and I did those steps. https://techcommunity.microsoft.com/t5/azure-synapse-analytics/how-to-securely-manage-load-credentials-with-ssdt-azure-key/bc-p/1397979

In my release pipeline I have this setting to deploy my SSDT project. As you can see i am using the values from my Azure Key Vault.

- task: AzureKeyVault@1
  inputs:
   azureSubscription: '<My Azure Subscription>'
   KeyVaultName: '<My Key Vault>'
   SecretsFilter: '*'
...
- task: SqlAzureDataWarehouseDacpacDeployment@1
  inputs:
    azureSubscription: '<My Azure Subscription>'
    AuthenticationType: 'server'
    ServerName: 'ABC.database.windows.net'
    DataWarehouse: '$(SynapseName)'
    SqlUsername: '$(SynapseSQLUsername)'
    SqlPassword: '$(SynapseSQLPassword)'
    deployType: 'DacpacTask'
    DeploymentAction: 'Publish'
    DacpacFile: 'SQL_ASynapse\bin\Release\SQL_ASynapse.dacpac'
    AdditionalArguments: '/p:IgnoreAnsiNulls=True /p:IgnoreComments=True /v:DatabaseScopeCredentialSecret=$(DatabaseScopeCredentialSecret) /v:DatabaseScopeCredentialIdentity=$(DatabaseScopeCredentialIdentity) /v:ExternalDataSourceMarineTrafficLocation=$(ExternalDataSourceMarineTrafficLocation)'
    IpDetectionMethod: 'AutoDetect'

I am passing three value for my three variable in my two blow scripts.

$(DatabaseScopeCredentialSecret)
$(DatabaseScopeCredentialIdentity) 
$(ExternalDataSourceMarineTrafficLocation)

I have below code in two separated SQL files.

ADLSCredential.sql :

CREATE MASTER KEY;
GO
CREATE DATABASE SCOPED CREDENTIAL ADLSCredential
WITH
    IDENTITY = '$(DatabaseScopeCredentialIdentity)',
    SECRET = '$(DatabaseScopeCredentialSecret)'
;

AzureDataLakeStoreMarineTraffic.sql :

CREATE EXTERNAL DATA SOURCE AzureDataLakeStoreMarineTraffic
WITH (
    TYPE = HADOOP,
    LOCATION='$(ExternalDataSourceMarineTrafficLocation)', 
    CREDENTIAL = ADLSCredential
);

When I don't have those objects on my DW (Synapse), My pipeline is able to find values from Azure key Vault and assign to my parameters and create both objects but next time I have below error.

 ##[error]*** Could not deploy package.
 ##[error]Warning SQL72013: The following SqlCmd variables are not defined in the target scripts: DatabaseScopeCredentialSecret DatabaseScopeCredentialIdentity ExternalDataSourceMarineTrafficLocation.
Error SQL72014: .Net SqlClient 

It seams when I don't need to ran those scripts, by passing values to my parameters SQLCMD has problem to find those variables because it had not created them.

Is there any way to have public variable somewhere or to tell SQLCMD to do not pass values for secound time?

Answer 1

I found the problem that I had.

I forgot to create variables in SSDT project. After creating them, every things work well.