SqlAzureDacpacDeployment@1 - Azure SQL Database deployment v1 task

Use this task to deploy an Azure SQL Database using DACPAC, or run scripts using SQLCMD.

Syntax

# Azure SQL Database deployment v1
# Deploy an Azure SQL Database using DACPAC or run scripts using SQLCMD.
- task: SqlAzureDacpacDeployment@1
  inputs:
    #azureConnectionType: 'ConnectedServiceNameARM' # 'ConnectedServiceName' | 'ConnectedServiceNameARM'. Alias: ConnectedServiceNameSelector. Azure Service Connection Type. Default: ConnectedServiceNameARM.
    #azureClassicSubscription: # string. Alias: ConnectedServiceName. Required when ConnectedServiceNameSelector = ConnectedServiceName. Azure Classic Subscription. 
    azureSubscription: # string. Alias: ConnectedServiceNameARM. Required when ConnectedServiceNameSelector = ConnectedServiceNameARM. Azure Subscription. 
  # SQL Database
    AuthenticationType: 'server' # 'server' | 'aadAuthenticationPassword' | 'aadAuthenticationIntegrated' | 'connectionString' | 'servicePrincipal'. Required. Authentication Type. Default: server.
    #ServerName: # string. Required when AuthenticationType = server || AuthenticationType = aadAuthenticationPassword || AuthenticationType = aadAuthenticationIntegrated || AuthenticationType = servicePrincipal. Azure SQL Server. 
    #DatabaseName: # string. Required when AuthenticationType = server || AuthenticationType = aadAuthenticationPassword || AuthenticationType = aadAuthenticationIntegrated || AuthenticationType = servicePrincipal. Database. 
    SqlUsername: # string. Required when AuthenticationType = server. Login. 
    SqlPassword: # string. Required when AuthenticationType = server. Password. 
    #aadSqlUsername: # string. Required when AuthenticationType = aadAuthenticationPassword. Login. 
    #aadSqlPassword: # string. Required when AuthenticationType = aadAuthenticationPassword. Password. 
    #ConnectionString: # string. Required when AuthenticationType = connectionString. Connection String. 
  # Deployment Package
    deployType: 'DacpacTask' # 'DacpacTask' | 'SqlTask' | 'InlineSqlTask'. Alias: TaskNameSelector. Required. Deploy type. Default: DacpacTask.
    DeploymentAction: 'Publish' # 'Publish' | 'Extract' | 'Export' | 'Import' | 'Script' | 'DriftReport' | 'DeployReport'. Required when TaskNameSelector = DacpacTask. Action. Default: Publish.
    #DacpacFile: # string. Required when DeploymentAction = Publish || DeploymentAction = Script || DeploymentAction = DeployReport. DACPAC File. 
    #BacpacFile: # string. Required when DeploymentAction = Import. BACPAC File. 
    #SqlFile: # string. Required when TaskNameSelector = SqlTask. SQL Script. 
    #SqlInline: # string. Required when TaskNameSelector = InlineSqlTask. Inline SQL Script. 
    #PublishProfile: # string. Optional. Use when TaskNameSelector = DacpacTask || DeploymentAction = Script || DeploymentAction = DeployReport. Publish Profile. 
    #AdditionalArguments: # string. Optional. Use when TaskNameSelector = DacpacTask || DeploymentAction = Extract || DeploymentAction = Export || DeploymentAction = Import || DeploymentAction = Script || DeploymentAction = DeployReport || DeploymentAction = DriftReport. Additional SqlPackage.exe Arguments. 
    #SqlAdditionalArguments: # string. Optional. Use when TaskNameSelector = SqlTask. Additional Invoke-Sqlcmd Arguments. 
    #InlineAdditionalArguments: # string. Optional. Use when TaskNameSelector = InlineSqlTask. Additional Invoke-Sqlcmd Arguments. 
  # Firewall
    IpDetectionMethod: 'AutoDetect' # 'AutoDetect' | 'IPAddressRange'. Required. Specify Firewall Rules Using. Default: AutoDetect.
    #StartIpAddress: # string. Required when IpDetectionMethod = IPAddressRange. Start IP Address. 
    #EndIpAddress: # string. Required when IpDetectionMethod = IPAddressRange. End IP Address. 
    #DeleteFirewallRule: true # boolean. Delete Rule After Task Ends. Default: true.

Inputs

azureConnectionType - Azure Service Connection Type
Input alias: ConnectedServiceNameSelector. string. Allowed values: ConnectedServiceName (Azure Classic), ConnectedServiceNameARM (Azure Resource Manager). Default value: ConnectedServiceNameARM.

azureClassicSubscription - Azure Classic Subscription
Input alias: ConnectedServiceName. string. Required when ConnectedServiceNameSelector = ConnectedServiceName.

Specifies the target Azure classic subscription for deploying SQL files.

azureSubscription - Azure Subscription
Input alias: ConnectedServiceNameARM. string. Required when ConnectedServiceNameSelector = ConnectedServiceNameARM.

Specifies the target Azure Resource Manager subscription for deploying SQL files.

AuthenticationType - Authentication Type
string. Required. Allowed values: server (SQL Server Authentication), aadAuthenticationPassword (Active Directory - Password), aadAuthenticationIntegrated (Active Directory - Integrated), connectionString (Connection String), servicePrincipal (Service Principal). Default value: server.

Specifies the type of database authentication. It can be an SQL Server, Active Directory (integrated), Active Directory (password), connection string, or service principal authentication. Integrated authentication means that the agent accesses the database using its current Active Directory account context.

Specify the option to connect to the Azure SQL Server database. You can provide the Azure SQL Server database details, the SQL Server connection string, AD Authentication (password or integrated), or use a service principal. For SQL Server authentication, use the SQL Server's user credentials. For AD authentication, use the credentials for the AD user configured to the SQL Server.

ServerName - Azure SQL Server
string. Required when AuthenticationType = server || AuthenticationType = aadAuthenticationPassword || AuthenticationType = aadAuthenticationIntegrated || AuthenticationType = servicePrincipal.

Specifies the Azure SQL Server name, like Fabrikam.database.windows.net.placeholder,1433 or Fabrikam.database.windows.net.placeholder.

DatabaseName - Database
string. Required when AuthenticationType = server || AuthenticationType = aadAuthenticationPassword || AuthenticationType = aadAuthenticationIntegrated || AuthenticationType = servicePrincipal.

Specifies the name of the Azure SQL database where the files are deployed.

SqlUsername - Login
string. Required when AuthenticationType = server.

Specifies the Azure SQL Server administrator login.

SqlPassword - Password
string. Required when AuthenticationType = server.

Specifies the password for the Azure SQL Server administrator. Variables defined in the build or release pipelines as $(passwordVariable) are accepted. You can mark the variable type as secret to secure it.

aadSqlUsername - Login
string. Required when AuthenticationType = aadAuthenticationPassword.

Specifies the Active Directory user name.

aadSqlPassword - Password
string. Required when AuthenticationType = aadAuthenticationPassword.

Specifies the password for the Active Directory user. Variables defined in the build or release pipelines as $(passwordVariable) are accepted. You can mark the variable type as secret to secure it.

ConnectionString - Connection String
string. Required when AuthenticationType = connectionString.

Specifies the Azure SQL Server connection string, like Server=testServer.database.windows.net.placeholder;Database=testdb;User ID=AccountPlaceholder;Password=$(securePassword);.

deployType - Deploy type
Input alias: TaskNameSelector. string. Required. Allowed values: DacpacTask (SQL DACPAC File), SqlTask (SQL Script File), InlineSqlTask (Inline SQL Script). Default value: DacpacTask.

DeploymentAction - Action
string. Required when TaskNameSelector = DacpacTask. Allowed values: Publish, Extract, Export, Import, Script, DriftReport (Drift Report), DeployReport (Deploy Report). Default value: Publish.

Specifies one of the SQL actions from the list. Learn more about the SQL actions list.

DacpacFile - DACPAC File
string. Required when DeploymentAction = Publish || DeploymentAction = Script || DeploymentAction = DeployReport.

Specifies the location of the DACPAC file on the automation agent or on a UNC path that's accessible to the automation agent, like \\BudgetIT\Web\Deploy\FabrikamDB.dacpac. Predefined system variables, like $(agent.releaseDirectory), can also be used.

BacpacFile - BACPAC File
string. Required when DeploymentAction = Import.

Specifies the location of the BACPAC file on the automation agent or on a UNC path that's accessible to the automation agent, like \\BudgetIT\Web\Deploy\FabrikamDB.bacpac. Predefined system variables, like $(agent.releaseDirectory), can also be used.

SqlFile - SQL Script
string. Required when TaskNameSelector = SqlTask.

Specifies the location of the SQL script file on the automation agent or on a UNC path that's accessible to the automation agent, like \\BudgetIT\Web\Deploy\FabrikamDB.sql. Predefined system variables, like $(agent.releaseDirectory), can also be used.

SqlInline - Inline SQL Script
string. Required when TaskNameSelector = InlineSqlTask.

Specifies the SQL script to execute on the previously selected database.

PublishProfile - Publish Profile
string. Optional. Use when TaskNameSelector = DacpacTask || DeploymentAction = Script || DeploymentAction = DeployReport.

Provides fine-grained control over Azure SQL database creation or upgrades.
Specifies the path to the publish profile XML file on the automation agent machine or on a UNC share. If the publish profile contains secrets, like credentials, upload it to the secure files library where it is securely stored with encryption. Next, use the Download secure file task at the start of your pipeline to download it to the agent machine when the pipeline runs. Delete it when the pipeline is complete. Predefined system variables, like $(agent.buildDirectory) or $(agent.releaseDirectory), can also be used.

AdditionalArguments - Additional SqlPackage.exe Arguments
string. Optional. Use when TaskNameSelector = DacpacTask || DeploymentAction = Extract || DeploymentAction = Export || DeploymentAction = Import || DeploymentAction = Script || DeploymentAction = DeployReport || DeploymentAction = DriftReport.

Specifies the additional SqlPackage.exe arguments that will be applied when deploying the Azure SQL database if the DACPAC option is selected, like /p:IgnoreAnsiNulls=True /p:IgnoreComments=True. These arguments will override the settings in the publish profile XML file (if provided).

SqlAdditionalArguments - Additional Invoke-Sqlcmd Arguments
string. Optional. Use when TaskNameSelector = SqlTask.

Specifies the additional Invoke-Sqlcmd arguments that are applied when executing the given SQL query on the Azure SQL database, like -ConnectionTimeout 100 -OutputSqlErrors.

InlineAdditionalArguments - Additional Invoke-Sqlcmd Arguments
string. Optional. Use when TaskNameSelector = InlineSqlTask.

Specifies the additional Invoke-Sqlcmd arguments that are applied when executing the given SQL query on the Azure SQL Database, like -ConnectionTimeout 100 -OutputSqlErrors.

IpDetectionMethod - Specify Firewall Rules Using
string. Required. Allowed values: AutoDetect, IPAddressRange. Default value: AutoDetect.

For the task to run, the IP address of the automation agent must be added to the Allowed IP Addresses in the Azure SQL Server's firewall. Select auto-detect to automatically add the firewall exception for the range of the possible IP address of the automation agent, or specify the range explicitly.

StartIpAddress - Start IP Address
string. Required when IpDetectionMethod = IPAddressRange.

Specifies the starting IP address of the automation agent machine pool, like 196.21.30.50.

EndIpAddress - End IP Address
string. Required when IpDetectionMethod = IPAddressRange.

Specifies the ending IP address of the automation agent machine pool, like 196.21.30.65.

DeleteFirewallRule - Delete Rule After Task Ends
boolean. Default value: true.

If selected, after the task ends, the IP addresses specified here are deleted from the Allowed IP Addresses list in the Azure SQL Server's firewall.

Task control options

All tasks have control options in addition to their task inputs. For more information, see Control options and common task properties.

Output variables

This task defines the following output variables, which you can consume in downstream steps, jobs, and stages.

SqlDeploymentOutputFile
The generated output file path when the deployment package action is Extract, Export, Script, DriftReport, or DeployReport.

Remarks

Use this task to deploy an Azure SQL database using a DACPAC, or run scripts using SQLCMD.

Important

This task is only supported in a Windows environment. If you are trying to use Azure Active Directory (Azure AD) integrated authentication, you must create a private agent. Azure AD integrated authentication is not supported for hosted agents.

Requirements

Requirement	Description
Pipeline types	YAML, Classic build, Classic release
Runs on	Agent
Demands	Self-hosted agents must have capabilities that match the following demands to run jobs that use this task: sqlpackage
Capabilities	This task does not satisfy any demands for subsequent tasks in the job.
Command restrictions	Any
Settable variables	Any
Agent version	1.103.0 or greater
Task category	Deploy