SqlDacpacDeploymentOnMachineGroup@0 - SQL Server database deploy v0 task

Use this task to deploy a SQL Server database using DACPAC or SQL scripts.

Syntax

# This task is supported on classic release pipelines only.
# Use the classic designer to add and configure this task in a classic release pipeline.
# See the following Inputs section for details on the inputs that this task supports.
# YAML Syntax is not supported in TFS 2018.
# Use the classic designer to add and configure tasks.
# See the following Inputs section for details on the inputs that this task supports.

Inputs

TaskType - Deploy SQL Using
string. Required. Allowed values: dacpac (Sql Dacpac), sqlQuery (Sql Query File), sqlInline (Inline Sql). Default value: dacpac.

Specifies the way you want to deploy the database: using Dacpac or SQL Scripts.


DacpacFile - DACPAC File
string. Required when TaskType = dacpac.

Specifies the location of the DACPAC file on the target machines or on a UNC path, like \\BudgetIT\Web\Deploy\FabrikamDB.dacpac. The UNC path should be accessible to the machine's administrator account. Environment variables are also supported, like $env:windir, $env:systemroot, or $env:windir\FabrikamFibre\DB. Wildcards can be used. For example, **/*.dacpac for the DACPAC file that's present in all sub folders.


SqlFile - Sql File
string. Required when TaskType = sqlQuery.

Specifies the location of the SQL file on the target. Provide a semi-colon separated list of SQL script files to execute multiple files. The SQL scripts are executed in the order given. The location can also be a UNC path, like \\BudgetIT\Web\Deploy\FabrikamDB.sql. The UNC path should be accessible to the machine's administrator account. Environment variables are also supported, like $env:windir, $env:systemroot, or $env:windir\FabrikamFibre\DB. Wildcards can be used. For example, **/*.sql for the SQL file present in all sub folders.


ExecuteInTransaction - Execute within a transaction
boolean. Optional. Use when TaskType = sqlQuery. Default value: false.

Executes the SQL script(s) within a transaction.


ExclusiveLock - Acquire an exclusive app lock while executing script(s)
boolean. Optional. Use when ExecuteInTransaction = true. Default value: false.

Acquires an exclusive app lock while executing script(s).


AppLockName - App lock name
string. Required when ExclusiveLock = true.

Specifies the app lock name.


InlineSql - Inline Sql
string. Required when TaskType = sqlInline.

Specifies the SQL queries inline.


TargetMethod - Specify SQL Using
string. Required when TaskType = dacpac. Allowed values: server, connectionString (Connection String), publishProfile (Publish Profile). Default value: server.

Specifies the option to connect to the target SQL Server database. You can provide the SQL Server database details, the SQL Server connection string, or the publish profile XML file.


ServerName - Server Name
string. Required when TargetMethod = server || TaskType = sqlQuery || TaskType = sqlInline. Default value: localhost.

Specifies the SQL Server name, like machinename\FabriakmSQL,1433, localhost, or .\SQL2012R2. Specifying localhost will connect to the default SQL Server instance on the machine.


DatabaseName - Database Name
string. Required when TargetMethod = server || TaskType = sqlQuery || TaskType = sqlInline.

Specifies the name of the SQL Server database.


AuthScheme - Authentication
string. Required when TargetMethod = server || TaskType = sqlQuery || TaskType = sqlInline. Allowed values: windowsAuthentication (Windows Authentication), sqlServerAuthentication (SQL Server Authentication). Default value: windowsAuthentication.

Specifies the authentication mode for connecting to the SQL Server. In Windows authentication mode, the account used to configure the deployment agent is used to connect to the SQL Server. In SQL Server authentication mode, the SQL login and password must be provided in the parameters below.


SqlUsername - SQL User name
string. Required when AuthScheme = sqlServerAuthentication.

Specifies the SQL login to connect to the SQL Server. This option is only available if SQL Server authentication mode has been selected.


SqlPassword - SQL Password
string. Required when AuthScheme = sqlServerAuthentication.

Specifies the password of the SQL login. This option is only available if SQL Server authentication mode has been selected.


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

Specifies the SQL Server connection string, like Server=localhost;Database=Fabrikam;User ID=AccountPlaceholder;Password=PasswordPlaceholder;.


PublishProfile - Publish Profile
string. Optional. Use when TaskType = dacpac.

Provides fine-grained control over SQL Server database deployments.
Specifies the path to the publish profile XML file on the target machine or on a UNC share that is accessible by the machine administrator's credentials.


AdditionalArguments - Additional Arguments
string. Optional. Use when TaskType = dacpac.

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


AdditionalArgumentsSql - Additional Arguments
string. Optional. Use when TaskType = sqlQuery || TaskType = sqlInline.

Specifies additional Invoke-Sqlcmd arguments that are applied when deploying the SQL Server database.


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

None.

Requirements

Requirement Description
Pipeline types Classic release
Runs on Agent, DeploymentGroup
Demands None
Capabilities This task does not satisfy any demands for subsequent tasks in the job.
Command restrictions Any
Settable variables Any
Agent version 1.102.0 or greater
Task category Deploy
Requirement Description
Pipeline types Classic release
Runs on DeploymentGroup
Demands None
Capabilities This task does not satisfy any demands for subsequent tasks in the job.
Command restrictions Any
Settable variables Any
Agent version 1.102.0 or greater
Task category Deploy