drjohnt / azuredevopsextensionsforsqlserver

Microsoft SQL Server deployment extensions for Azure DevOps Pipelines

Azure DevOps Extensions for SQL Server

This repo contains several extensions for Azure DevOps that help in deploying a wide variety of Microsoft SQL Server components such as Analysis Services tabular cubes, SQL Server databases and Integration Services projects.

Azure DevOps Exensions

Azure DevOps Extension	Description and Project Link	Build
	Deploys a SQL Server Database DACPAC to a server using a DAC Publish Profile
	Deploy, update and drop a tabular cube model on Microsoft SQL Server Analysis Services (SSAS)
	Run single or multiple SQLCMD Scripts passing multiple SQLCMD variables

The associated project wiki explains the basics of applying DevOps to your Data Warehouse and outlines exactly how to setup your in-house build server, Azure Pipelines and test suites.

Note that the Publish DACPAC using a DAC Publish Profile extension is also published as Deploy Database DACPAC using a DAC Publish Profile using the same codebase as most people thing Deploy rather than Publish.

azuredevopsextensionsforsqlserver's Issues

[BUG] Get-SqlPackagePath.ps1 does not find SqlPackage.exe if installed as part of a SQL Server installation

For example, with a freshly installed installation of SQL Server 2017 on Windows Server 2019 using Chocolately, the correct location for SqlPackage.exe is:

C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe

This path (or the equivalent path for any other version of SQL Server - see here) is not searched as part of Get-SqlPackagePath.ps1.

The workaround appears to be to specify a 'CustomSqlPackageInstallLocation' environment variable, but the script would be more user-friendly if it searched all the default SQL Server install locations.

[ENHANCMENT] TrustServerCertificate

Is there a way to get the Invoke-Sqlcmd to pass -TrustServerCertificate ?

[BUG] DeployScript and DeployReport Task Options Not Used

Component

• PublishDacPac
• DeployDatabase

Describe the bug
When using the DeployScriptPath and/or DeployReportPath task options, they are not honored during task execution.
See this line in the PublishDacPacTask:

AzureDevOpsExtensionsForSqlServer/extensions/PublishDacPac/PublishDacPacTask/PublishDacPacTask.ps1

Line 91 in 83c6e88

if ("$DeployScriptPath" -ne "") {

Variable: $DeployScriptPath or $DeployReportPath is never assigned at the beginning of the task where all the task inputs are collected, starting at:

AzureDevOpsExtensionsForSqlServer/extensions/PublishDacPac/PublishDacPacTask/PublishDacPacTask.ps1

Line 24 in 83c6e88

[string]$DacPacPath = Get-VstsInput -Name "DacPacPath" -Require;

To Reproduce
Run PublishDacPacTask with DeployScriptPath and DeployReportPath populated (not empty). The result will be an actual publish action to the target database rather than creating a deployment script.

Expected behavior
When DeployScriptPath is not an empty string, a publish script should be created, the database should NOT be updated.

Screenshots

Azure DevOps Agent
Self-Hosted Agent: Version 2.144.2

[ENHANCMENT] Provide a .dat file where we can put the order of the scripts execution

Component
RunSqlCmdScripts

Is your feature request related to a problem? Please describe.
It's more complicated to manage the execution order of the scripts by renaming them, the scripts are managed by different teams and a change in 1 name would imply that all the other teams renamed their scripts

Describe the solution you'd like
Provide a .dat file for the task where the scripts that should be executed are listed 1 per line.
Example of dsc.dat file:
jobs\appjob.sql
jobs\serverjob.sql
app_permissions\appname.sql
app_permissions\appxpto.sql

This already exists in another extension and works well, also gives flexibility if we wan't to temporarily "disable" a script execution by removing that script from the .dat file instead of removing the script from source control.

Describe alternatives you've considered
Didn't considered other alternatives, because I worked like this in another extension and it worked well, just moved to this one because the other didn't provide a readable output when an error occured when executing scripts

Additional context

The extension I mention is SQL Toolkit parameter $executionOrder in this file

Enhancement | Allow SQL Username and Password

Would it possible to pass through SQL Username and Password? This would allow the Extension to use both Integrated security (as it does now) and sql authentication with a minor change.

It could look something like:

foreach ($SqlCmdFile in $SqlCmdFiles) {
	$Command = {Invoke-Sqlcmd -Server $Server -Database $Database -InputFile $SqlCmdFile -QueryTimeout $QueryTimeout -ErrorAction Stop}
	# Now Invoke-Sqlcmd for each script in the folder
	Write-Host "Running SQLCMD file:   $(Split-Path -Leaf $SqlCmdFile)"
	if ($SqlCmdVariableType -ne 'none') {
		$Command = "$Command -Variable $SqlCmdVariables"
	}
	if ($SqlCmdUsername -ne 'none') {
		$Command = "$Command -Username $Username -Password $Password"
	}	
	Invoke-Command -ScriptBlock $Command
}

PublishDacPac Feature Request: Generate schema change script action

I am trying to allow a pipeline to publish a schema change to an on-premises SQL Server 2017 instance, but I want to do that in two steps:

• Generate schema change script action
• After approval, publish

I know that can be achieved by publishing to SQL Azure by setting deploymentAction: 'Script' and then deploymentAction: 'Publish'

Can this be done by using this extension? I have gone through the documentation, and it does not seem possible. If so, can you provide an example?

This task is not converting datatype from int to float in one of my table.

Hi Team,

I am using this task in Azure DevOps, this task was working perfectly since last week. But since last week it is behaving differently.

I have a table, in which a column datatype was [int], I had changed from [int] to [float] and performed the deployment. It didn't show any error but did not change the column's data type from [int] to [float].

Please help me to resolve this issue.

Regards
Ashish Jain
Email : [email protected]
Mobi : [phone]

[ENHANCMENT] To Be able to update SSAS Data Source Account to use Service Account with gMSA

for SSAS Tabular Cube Deployment Tools - Update SSAS tabular cube data source

[ENHANCMENT] To Be able to update SSAS Data Source Account to use Service Account with gMSA

Enhancement | RunSqlCmdScriptsInFolder.ps1 | Azure SQL

If you could expose all Authentication Types in Invoke-SqlCmd (AAD with U/P, Connection String) that will be great. Currently, we cannot use this extension if we want to provide username / password with AAD with Password authentication.

Refer:
https://github.com/DrJohnT/AzureDevOpsExtensionsForSqlServer/blob/master/extensions/RunSqlCmdScripts/RunSqlCmdScriptsInFolderTask/RunSqlCmdScriptsInFolder.ps1

you can refer to how this works:
https://github.com/microsoft/azure-pipelines-tasks/blob/master/Tasks/SqlAzureDacpacDeploymentV1/README.md

##[error]There are no batches in the input script.

Component

• RunSqlCmdScripts

Describe the bug
Having problems only with the create a table using RunSqlCmdScripts in Synapse.

2021-06-22T20:17:32.2933212Z ##[section]Starting: Run SQLCMD Scripts in folder D:\a\r1\a/_Synapse-test-CI/drop
2021-06-22T20:17:32.3144629Z ==============================================================================
2021-06-22T20:17:32.3145030Z Task : Run all SQL Scripts in a folder in SqlCmd mode
2021-06-22T20:17:32.3145996Z Description : Run multiple SQL Scripts in a folder in SqlCmd mode and passing in parameters via a SqlCmdVariables array
2021-06-22T20:17:32.3146429Z Version : 1.1.8
2021-06-22T20:17:32.3146664Z Author : Dr. John Tunnicliffe
2021-06-22T20:17:32.3147823Z Help : More Information
2021-06-22T20:17:32.3148290Z ==============================================================================
2021-06-22T20:17:34.9141988Z ==============================================================================
2021-06-22T20:17:34.9164922Z Calling Invoke-SqlCmd with the following parameters:
2021-06-22T20:17:34.9239687Z Server: xxxxxxxxsynapse.database.windows.net
2021-06-22T20:17:34.9317290Z Database: synapse-sqlpool
2021-06-22T20:17:34.9353190Z SqlCmdSciptFolderPath: D:\a\r1\a_Synapse-test-CI\drop
2021-06-22T20:17:34.9374536Z Recursive: false
2021-06-22T20:17:34.9406743Z SqlCmdVariableType: none
2021-06-22T20:17:34.9429054Z AuthenticationUser: xxxx_test
2021-06-22T20:17:39.4673403Z SQLCMD folder: D:\a\r1\a_Synapse-test-CI\drop
2021-06-22T20:17:39.4769914Z Running SQLCMD file: 4-Create-Table-Sailing.sql
2021-06-22T20:17:39.5376742Z Invoke-Sqlcmd -ServerInstance 'devsynapse.database.windows.net' -Database dev-synapse-sqlpool -InputFile 'D:\a\r1\a_Synapse-test-CI\drop\4-Create-Table-Sailing.sql' -ErrorAction Stop -QueryTimeout 6000 -Credential $Credential
2021-06-22T20:17:43.1486666Z ##[error]There are no batches in the input script.
Msg 104309, Level 16, State 1, Procedure , Line 1.
2021-06-22T20:17:43.2156622Z ##[section]Finishing: Run SQLCMD Scripts in folder D:\a\r1\a/_Synapse-test-CI/drop

To Reproduce
SQL Statement to create table that is giving error

/****** Object: Table [dbo].[sailing_flags_test] Script Date: 5/14/2021 1:17:49 PM ******/
SET NOCOUNT OFF;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[sailing_flags_test]
(
[SHIP_CODE] varchar NULL,
[SAILING_DATE] [datetime] NULL,
[META_PRODUCT_CODE] varchar NULL,
[RDSS_PRODUCT_CODE] varchar NULL,
[CHARTER] varchar NULL,
[ITINERARY_NIGHTS] [float] NULL,
[ORIGINATING_PORT] varchar NULL,
[HOLIDAY] varchar NULL,
[SAILING_BUCKET] varchar NULL,
[BUCKET_GROUP] varchar NULL
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
)
GO

Expected behavior
A clear and concise description of what you expected to happen.

Screenshots
If applicable, add screenshots to help explain your problem.

Azure DevOps Agent

• Hosted
• Agent Version - vs2017-win2016 and windows-2019

Additional context
I am able to run other scripts Create Schema, Drop Schema, but just create table is giving error.

sql execution getting failed if Data already present

$Command = "Invoke-Sqlcmd -ServerInstance '$Server' -Database '$Database' -InputFile '$SqlCmdFile' -OutputSqlErrors 1 -ErrorAction Stop";

Scenario : lets say 5 SQL insertion task is there inside the folder as 5 files.
If issue with first SQL command file , Second command wont run and throw error
example ::: Cannot insert duplicate key in object 'dbo.Table_TEST'. The duplicate key value

So next sql cmd wont run in the for loop.

Please have a check.
Thanks

[ENHANCMENT] Hidden Password Field

Component
This repo covers four extensions for Azure DevOps. Please be clear which extension you are talking about. The short extension names are:

• RunSqlCmdScripts
• DeployTabularModel
• PublishDacPac
• DeployDatabase (technically the same as PublishDacPac but rebranded as people think 'Deploy' rather than 'Publish').

Is your feature request related to a problem? Please describe.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

DeployCube: username and password not being passed to update tabular model on Azure AS

Great extension! It's so far has saved me a lot of time. However, I do have a problem with passing a secure variable with my u/p to update the model. I looks like it updates but when I go to process it, no luck. The process model task says
The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: 'The following system error occurred: The user name or password is incorrect.
A connection could not be made to the data source with the Name of 'SqlAzure somewhereyoudontknow.database.windows.net datatrax'.
'..

I have validated the u/p several times and looked at the connection string in SSAS and it looks ok. using the same u/p to manually update works fine.

Error - With Run SQLCMD Scripts in folder

I keep getting "Value cannot be null" not sure which value.See the attached full log with error message
Error.docx

Multiple instances

Is there a way we can use these products to deliver a database to over 100 instances?

Run Sql Command Scripts in Folder - Powershell loading issue

I tried to use this extension and got an odd error when it went to run. It looks like it tried to load Powershell to run with and failed. Ever seen this before?

2021-01-27T17:04:14.8918526Z Calling Invoke-SqlCmd with the following parameters:
2021-01-27T17:04:14.8925868Z Server: xxxx
2021-01-27T17:04:14.8933072Z Database: xxxxx
2021-01-27T17:04:14.8941333Z SqlCmdSciptFolderPath: C:\azagent\A2_work\r1\a\xxxxxxx
2021-01-27T17:04:14.8948958Z Recursive: true
2021-01-27T17:04:14.8958216Z SqlCmdVariableType: none
2021-01-27T17:04:14.9501878Z Installing PowerShell module SqlServer for current user
2021-01-27T17:04:25.1524571Z ##[warning]MSG:UnableToDownload «https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409» «»
2021-01-27T17:04:25.1542932Z ##[warning]Unable to download the list of available providers. Check your internet connection.
2021-01-27T17:04:25.2357417Z ##[warning]Unable to download from URI 'https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409' to ''.
2021-01-27T17:04:25.5172481Z ##[error]No match was found for the specified search criteria for the provider 'NuGet'. The package provider requires 'PackageManagement' and 'Provider' tags. Please check if the specified package has the tags.

Error on Update SSAS tabular cube data source

Hey,

thanks for your contribution, you have done an amazing job,
I am trying to deploy a tabular cube 1400 model with your Azure addon and it succeeds but when I am using the task "Update SSAS tabular cube data source" in order to change connection string of the data source (we have many environments as you guess) I get the following error

"Cannot bind argument to parameter 'InputObject' because it is null."

It doesn't help much debugging the error but I would be glad if you have some ideas on that.

Thanks,
Achilles

[ENHANCMENT] Display error log file after successful deployment

** DeployTabularModel **

Is your feature request related to a problem? Please describe.
The error log file contains useful information even for successful deployments, it would be useful to display this in the console:
Connecting to the SERVERNAME server
Database, DATABASENAME, found on server, SERVERNAME. Applying configuration settings and options...
Analyzing configuration settings...
Done
Analyzing storage information...
Done
Analyzing security information...
Done
Generating processing sequence...
Deploying the 'DATABASENAME' database to 'SERVERNAME'.
Done

Describe the solution you'd like
Display AnalysisServicesDeploymentExeLog.txt in console after successful deployment

Describe alternatives you've considered
Add powershell task after deployment to display log file or publish log file to pipeline artifacts

DeployCube: Issues using wildcards with asdatabse path in SSAS Tabular Cube Deployment Tools Extension in AzureDevOps

I am experiencing an issue when attempting to use a wildcard in the 'Path to .asdatabse file' option of the 'Deploy SSAS tabular model' step in the pipeline.

If I specify the full path to the .asdatabse file it works without an issue and successfully deploys. However, as soon as I take the name of the file out and replace this with a wildcard the deployment fails and the only error I receive in the build log is as follows:

2020-03-30T01:53:57.3366041Z ##[error]Error: Error executing C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe

I would like to use the wildcard option as its likely that future Tabular model projects will have multiple tabular models and for Continuous Integration it would be ideal to have these deploy, without the need to customise each build script/pipeline

Any help you can provide would be greatly appreciated.

Run SQL / SQLCMD Scripts passing multiple SQLCMD variables

Will this tool identify the previously ran scripts? Because I don't want to rerun the scripts each build/release want to run only newly added scripts. So is there a way it can track and not run the scripts ? May be by filename or something?

[BUG]: Update SSAS tabular cube data source(s) only updating first connection

Component

• DeployTabularModel

Describe the bug
I have SSAS Tabular Comp.Level 1500 with two connection, using "Native OLE DB\SQL Server Native Client 11" provider:

• 1.connection Server: BIDEV Database: Utilities
• 2.connection Server: BIDEV Database: Data

I am trying to update data source with "Update SSAS tabular cube data source"
I added two steps for the release agent, only distinct by the "Name of source SQL Database": Utilities and Data

It seems both steps are updating the first connection, and this is my result:

• 1.connection Server: BITEST Database: Data
• 2.connection Server: BIDEV Database: Data

Expected behavior

• 1.connection Server: BITEST Database: Utilities
• 2.connection Server: BITEST Database: Data

Azure DevOps Agent

• In-house hosted:
• Agent Version: 2.182.1

Difference Between "Deploy DB" and "Publish DB" Pipelines

What is the difference between these 2 pipeline components?

Deploy Database DACPAC using a DAC Publish Profile
https://marketplace.visualstudio.com/items?itemName=DrJohnExtensions.DeployDatabase&ssr=false#overview

Publish DACPAC using a DAC Publish Profile
https://marketplace.visualstudio.com/items?itemName=DrJohnExtensions.PublishDacPac&ssr=false#overview

Cannot publish a script using Publish DACPAC extension

Can I publish a script using this extension?

What is the minimum requirement of SSMS

What is the minimum requirement of SSMS for the task "Deploy database DACPAC using a DAC Publish Profile"?

Server does not exist error

I am getting a server does not exist error. I have the Agent setup on my desktop computer and I can connect to the SQL Server from Management Studio from same computer. I am unsure how to troubleshoot this further.

2019-06-18T03:31:31.4844507Z ##[error]Error: Server 'XXXXX.xxxxxx.us-east-2.rds.amazonaws.com' does not exist!

Also how do you recommend passing the SQL login password to the task. I have the login in the publish.xml file.

SSAS Extension issue on prem azure DevOps 2019

Hello,

When using the drop SSAS release task, we are getting the following error:

2019-09-19T16:17:53.8666754Z ##[error]SSAS Server [Server name] not found.

Is this extension compatible with On-Prem Azure DevOps Server?

Thank you,
Vini

Are Azure DevOps release variables supported?

[ENHANCMENT] Supporting DataSource parameter of Update-TabularCubeDataSource function in UpdateTabularCubeDataSourceTask

Component

• DeployTabularModel

Is your feature request related to a problem? Please describe.
My tabular cube has more data sources, but using this extension I cannot update all data sources, only the first.
As I saw, the newest PS module supports data source name parameter, but the packaged module (with the extension) is not the newest, and the parameter the parameter is not mapped to the interface.

Describe the solution you'd like
A new input parameter in UpdateTabularCubeDataSourceTask for the DataSource parameter of Update-TabularCubeDataSource function.

SqlServer Module not imported, Ping-SqlServer fails

Hi,

I am currently evaluating DevOps CI & CD for us and I noticed your great addin, appreciate the hard work.

So I have set up a build server, installed SSMS and SqlServer from the PowerShell gallery.
Afterwards, I have tried to run deploy my dacpac with a publish profile, which failed with the error:

##[error]Error: Error Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.

I investigated a bit within the code and this was raised by Ping-SqlServer submodule. I installed this from the PowerShell gallery as well and faced the same issue in the console. I had to enter Import-Module SqlServer, and subsequent runs would run fine immediately.

As I am not a PS expert, I do not know if the issue is my setup or if an explicit Import should be included.
During investigation, I found out that the powershell profile (neither system nor user) is used for DevOps Build Agents, so I had to modify the files within the Agent Task Directory to Include the Import statement.

Maybe you can add an explicit Import or have an Idea for me where to look?

Thanks!

Deploy SSAS tabular cube model Version : 1.1.8 error

Component:
DeployTabularModel

Description: The component has been running ok at Version 1.1.7.
However, since it was auto updated to version : 1.1.8 the task stared to fail with the following error message:

2021-01-07T07:29:19.6200982Z ##[section]Starting: Deploy SSAS tabular model
2021-01-07T07:29:19.6300444Z ==============================================================================
2021-01-07T07:29:19.6300759Z Task : Deploy SSAS tabular cube model
2021-01-07T07:29:19.6301020Z Description : Publish a tabular cube model to an SSAS instance
2021-01-07T07:29:19.6301217Z Version : 1.1.8
2021-01-07T07:29:19.6301387Z Author : Dr. John Tunnicliffe
2021-01-07T07:29:19.6302268Z Help : More Information
2021-01-07T07:29:19.6302650Z ==============================================================================
2021-01-07T07:29:20.5999847Z ##[error]Cannot convert value "System.String" to type "System.Boolean". Boolean parameters accept only Boolean values and numbers, such as $True, $False, 1 or 0.
2021-01-07T07:29:20.6255362Z ##[section]Finishing: Deploy SSAS tabular model

Azure DevOps Agent
In-house hosted
Agent version 2.179.0

Additional information

Existing pipelines (set to run with the older ver 1.17) had to be updated

This warning disappeared when I updated the relevant Deployment option
Version of Microsoft.AnalysisServices.Deployment.exe to use for the deployment

Then I saved the pipeline and re-run the deployment pipeline with the updated task, which fails with the following error message:
[error]Cannot convert value "System.String" to type "System.Boolean". Boolean parameters accept only Boolean values and numbers, such as $True, $False, 1 or 0.

[BUG] newly upgrade OnPrev DevOps 2022.0.1 fails

AzureDevOpsServer_20230418.1
Been using this extension for a long time, just upgraded DevOps, now fails with:

The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

any help would be appreciated

RunSqlCmdScripts Feature Request: Log/Output file

Hi John,

Thank you so much for creating this very useful extension. Is it possible for you to add an option to add logging capabilities for each file to a specific location?

FYI, I tried using the Invoke-Sqlcmd from powershell with outputas param specified but no output is recorded for the Insert and Update statements (rows affected). Any help would be appreciated.

No match was found for the specified search criteria for the provider 'NuGet'

I get this error when I try to use the component in Azure devops pipeline. Is there anyone else that have had this error and know how to fix it?

##[warning]MSG:UnableToDownload «https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409» «»
##[warning]Unable to download the list of available providers. Check your internet connection.
##[warning]Unable to download from URI 'https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409' to ''.
##[error]No match was found for the specified search criteria for the provider 'NuGet'. The package provider requires 'PackageManagement' and 'Provider' tags. Please check if the specified package has the tags.

PublishDacPac - Unable to Login into Database using Sql Server Username and Password.

Hi Everyone,

I am using this Extension for Database Deployment Option. Actually i have to run queries on Azure Sql Databases. And those Databases don't support Windows Login. So i have to give Sql Server Username and Password.
But i have searched a lot and i didn't find any way to add my credentials in this Task. Can anyone please help me out how i can do that? My work is stuck due to this. :(

Azure Synapse is failing to perform the deployment giving error "type Microsoft.Data.Tools.Schema.Sql.SqlDwDatabaseSchemaProvider is not valid"

I am working on Azure Synapse. I am able to build the Azure Synapse project successfully using the Azure CI pipeline's MS Build task.

But as I am trying to deploy Azure Synapse using the Azure CD pipeline, I am getting the following error.

Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.SqlDwDatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service.

I am deploying the DacPac using the following task. I hope this should not be any concern.

https://github.com/DrJohnT/AzureDevOpsExtensionsForSqlServer/tree/master/extensions/PublishDacPac

This is a weird error because a couple of days ago same deployment was done successfully.

My Publish profile is below.

Please help!

[BUG] A parameter cannot be found that matches parameter name 'Credential'.

Component

• RunSqlCmdScripts

When i try to run the task with SQL Authentication it returns an error.

To Reproduce
My password variable comes from an Variable Group which is linked to an Azure Key Vault.

Azure DevOps Agent
Its a Windows Self Hosted Agent running behind a proxy server.

Trouble updating datasource for tabular model

It deploys alright, but when I try to update the data source it fails.

Getting this when trying to:

2020-02-06T20:57:52.8458415Z ##[section]Starting: Update SSAS tabular cube data source in Medlemsomraadet
2020-02-06T20:57:52.8588803Z ==============================================================================
2020-02-06T20:57:52.8589115Z Task         : Update SSAS tabular cube data source
2020-02-06T20:57:52.8589375Z Description  : Update SSAS tabular cube data source connection string to use an on-premise SQL Server database
2020-02-06T20:57:52.8589615Z Version      : 1.0.10
2020-02-06T20:57:52.8589785Z Author       : Dr. John Tunnicliffe
2020-02-06T20:57:52.8590077Z Help         : [More Information](https://github.com/DrJohnT/AzureDevOpsExtensionsForSqlServer/tree/master/extensions/DeployTabularModel)
2020-02-06T20:57:52.8590459Z ==============================================================================
2020-02-06T20:57:54.3274802Z Invoking Update-CubeDataSource from [DeployCube](https://github.com/DrJohnT/DeployCube) module with the following parameters:
2020-02-06T20:57:54.3286212Z AsServer:             BIDBtest
2020-02-06T20:57:54.3296119Z CubeDatabaseName:     Medlemsomraadet
2020-02-06T20:57:54.3306284Z SourceSqlServer:      BIDB
2020-02-06T20:57:54.3316046Z SourceSqlDatabase:    BI_DW
2020-02-06T20:57:54.3325707Z ImpersonationMode:    ImpersonateServiceAccount
2020-02-06T20:57:54.3335511Z ImpersonationAccount: 
2020-02-06T20:57:54.3345191Z ==============================================================================
2020-02-06T20:57:56.5782940Z ##[warning]MSG:UnableToDownload «https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409» «»
2020-02-06T20:57:57.7024267Z ##[warning]Unable to download from URI 'https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409' to ''.
2020-02-06T20:57:58.1330243Z ##[warning]Unable to download the list of available providers. Check your internet connection.
2020-02-06T20:57:59.7826156Z ==============================================================================
2020-02-06T20:57:59.9043554Z ##[error]Error Error No match was found for the specified search criteria for the provider 'NuGet'. The package provider requires 'PackageManagement' and 'Provider' tags. Please check if the specified package has the tags.
2020-02-06T20:57:59.9461846Z ##[section]Finishing: Update SSAS tabular cube data source in Medlemsomraadet

[BUG] "Deploy SSAS tabular cube model" don't add any new tables

Component
This repo covers four extensions for Azure DevOps. Please be clear which extension you are talking about. The short extension names are:

• RunSqlCmdScripts
• DeployTabularModel
• PublishDacPac
• DeployDatabase (technically the same as PublishDacPac but rebranded as people think 'Deploy' rather than 'Publish').

Describe the bug
I deployed a cube using Deploy SSAS tabular cube model and worked fine. I then added couple of more tables into model and tried deploying but it fails to see the changes. If I drop and deploy then it works fine. Is this by design or am I doing something wrong or not using correctly? Is it possible to deploy changes without dropping cube first?

To Reproduce
Deploy the changes

Expected behavior
New tables should be added into model

Screenshots

Azure DevOps Agent

• Hosted

Additional context

PublishDacPac: Unable to use wildcards in dacpac file name despite tool-tip sugggestion

I ran my first deploy today using the "Publish Schema using Publish Profile Task and it failed citing in the output that Could not load package from 'd:\a\11\s\Tools\Schema\bin\release\*.dacpac 'Illegal characters in path'. My original def path looked like this '$(System.DefaultWorkingDirectory)\Tools\Schema\bin\$(BuildConfiguration)\Schema.dacpac'. It seems this should be corrected so one can use wildcards (definitely nice to have) or update the tool-tip to reflect that one cannot use them.

Can't deploy database when not having PING access to target server

I have access to target server:
test-netconnection server -port 1433
but can't do a PING. In that scenario I'm getting the following error:

Error: Server '10.xx.xx.xx,1433' does not exist!

due to the following line of code:
if (!(Ping-SqlServer -Server $Server)) { throw "Server '$Server' does not exist!";

I'm not sure if testing for the existence of server via PING is the best idea.
Could you make this block optional?

Username / Password not exposed for DeployTabularModel task in Marketplace

Hi @DrJohnT ,

If I look into the code username / password is available to set on the deploy task.

However, the version on the marketplace does not have this.

Is this version not released yet? If not, what needs to happen before it can be released? We would like to use it for Azure Analysis Services deployment.

Thanks!

[ENHANCMENT]

Component
This repo covers four extensions for Azure DevOps. Please be clear which extension you are talking about. The short extension names are:

• RunSqlCmdScripts

Is your feature request related to a problem? Please describe.
I need to show output of the sql script execution (ex: rows effected, print messages)

Describe the solution you'd like
add a verbose

Publish DACPAC - Does Target server name overwrite the connection string?

Im confused as to what the target server is used for. In the profile we set a connection string and db. How are these other variables used?

Process SSAS Tabular cube model fails returning non descriptive error

Component
DeployTabularModel - Process SSAS Tabular cube model

Describe the bug
Process SSAS Tabular cube model fails returning error:
##[error]Invoke-ProcessTabularCubeDatabase: Error processing SalesCube on SSAS Server: correctservername.com

The component seems to fail before even connecting to SSAS server since SQL Profiler does not detect a login attempt. Failure occurs immediately after starting the task.

However previous task in the pipeline is Deploy SSAS Tabular cube model from the same extension and it is using the same ServerName and DatabaseName variables and works as expected.

Azure DevOps Agent

• In-house hosted
• Agent Version: 2.181.0

Credentials in Cube Deployment

Hello,

in the "Deploy SSAS tabular cube model" i cannot find any field passing the Credentials to deploy the Model to the Server.

Do i miss something?
How should this work?

Publish version 1.2.10 to for publish-dacpac VisualStudio Marketplace or Powershell gallery please

I saw in version 1.2.10 support for sqlpackage 160 is implemented, thank you for that.
However, the visualstudio marketplace version is still on 1.2.9, and therefor doesn't support sqlpackage 160 yet.

Also your Public powershell module (in the PSgallery) is still without support for sqlpackage.exe 160.
I thank you for the great work.

I could ofcourse clone the repo, and then execute publish-dacpac from there, but i really like your visual studio extension for the pipeline, so would be great if you could publish it.

Thank you in advance, Raymond

##[error]Incorrect syntax near '�'.

Getting incorrect syntax error "##[error]Incorrect syntax near '�'." but if I run the same sql script in management studio it works without any issue