WinRM SQL Server DB Deployment task

Azure Pipelines

Use this task in a build or release pipeline to deploy to SQL Server Database using a DACPAC or SQL script.

YAML snippet

# SQL Server database deploy
# Deploy a SQL Server database using DACPAC or SQL scripts
- task: SqlDacpacDeploymentOnMachineGroup@0
  inputs:
    #taskType: 'dacpac' # Options: dacpac, sqlQuery, sqlInline
    #dacpacFile: # Required when taskType == Dacpac
    #sqlFile: # Required when taskType == SqlQuery
    #executeInTransaction: false # Optional
    #exclusiveLock: false # Optional
    #appLockName: # Required when exclusiveLock == True
    #inlineSql: # Required when taskType == SqlInline
    #targetMethod: 'server' # Required when taskType == Dacpac# Options: server, connectionString, publishProfile
    #serverName: 'localhost' # Required when targetMethod == Server || TaskType == SqlQuery || TaskType == SqlInline
    #databaseName: # Required when targetMethod == Server || TaskType == SqlQuery || TaskType == SqlInline
    #authScheme: 'windowsAuthentication' # Required when targetMethod == Server || TaskType == SqlQuery || TaskType == SqlInline# Options: windowsAuthentication, sqlServerAuthentication
    #sqlUsername: # Required when authScheme == SqlServerAuthentication
    #sqlPassword: # Required when authScheme == SqlServerAuthentication
    #connectionString: # Required when targetMethod == ConnectionString
    #publishProfile: # Optional
    #additionalArguments: # Optional
    #additionalArgumentsSql: # Optional

Arguments

ArgumentDescription
Deploy SQL Using(Required) Specify the way in which you want to deploy DB, either by using Dacpac or by using Sql Script.
DACPAC File(Required) 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, such as $env:windir, $env:systemroot, $env:windir\FabrikamFibre\DB. Wildcards can be used. For example, /*.dacpac for DACPAC file present in all sub folders.
Sql File(Required) Location of the SQL file on the target. Provide semi-colon separated list of SQL script files to execute multiple files. The SQL scripts will be executed in the order given. 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, such as $env:windir, $env:systemroot, $env:windir\FabrikamFibre\DB. Wildcards can be used. For example, /*.sql for sql file present in all sub folders.
Execute within a transaction(Optional) Executes SQL script(s) within a transaction
Acquire an exclusive app lock while executing script(s)(Optional) Acquires an exclusive app lock while executing script(s)
App lock name(Required) App lock name
Inline Sql(Required) Sql Queries inline
Specify SQL Using(Required) Specify the option to connect to the target SQL Server Database. The options are either to provide the SQL Server Database details, or the SQL Server connection string, or the Publish profile XML file.
Server Name(Required) Provide the SQL Server name like, machinename\FabrikamSQL,1433 or localhost or .\SQL2012R2. Specifying localhost will connect to the Default SQL Server instance on the machine.
Database Name(Required) Provide the name of the SQL Server database.
Authentication(Required) Select the authentication mode for connecting to the SQL Server. In Windows authentication mode, the administrator's account, as specified in the Machines section, is used to connect to the SQL Server. In SQL Server Authentication mode, the SQL login and Password have to be provided in the parameters below.
SQL User name(Required) Provide the SQL login to connect to the SQL Server. The option is only available if SQL Server Authentication mode has been selected.
SQL Password(Required) Provide the Password of the SQL login. The option is only available if SQL Server Authentication mode has been selected.
Connection String(Required) Specify the SQL Server connection string like "Server=localhost;Database=Fabrikam;User ID=sqluser;Password=placeholderpassword;"
Publish Profile(Optional) Publish profile provide fine-grained control over SQL Server database deployments. Specify 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.
Additional Arguments(Optional) Additional SqlPackage.exe arguments that will be applied when deploying the SQL Server database like, /p:IgnoreAnsiNulls=True /p:IgnoreComments=True. These arguments will override the settings in the Publish profile XML file (if provided).
Additional Arguments(Optional) Additional Invoke-Sqlcmd arguments that will be applied when deploying the SQL Server database.
Control options

Open source

This task is open source on GitHub. Feedback and contributions are welcome.