Connecting to Microsoft Azure Analysis Services using TIBCO Spotfire connector for Microsoft SQL Server Analysis Services fails with error "Input String was not in a correct format"

Connecting to Microsoft Azure Analysis Services using TIBCO Spotfire connector for Microsoft SQL Server Analysis Services fails with error "Input String was not in a correct format"

book

Article ID: KB0076401

calendar_today

Updated On:

Products Versions
Spotfire Analyst All Versions

Description

When connecting to Microsoft Azure Analysis Services connection URL of format "asazure://..." using the TIBCO Spotfire connector for Microsoft SQL Server Analysis Services and ADOMD.NET Driver for SQL Server 2016, 2014 or 2012 installed on the machine, it may fail with the following error:

2020-01-22T10:23:35,315-06:00 2020-01-22 16:23:35,315 00udwi0wiLBWVtPjH356 [45] DEBUG Spotfire.Dxp.Data.Adapters.Ssas.SsasAdapterConnection [(null)] - Get databases
Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: The connection string is not valid. ---> System.FormatException: Input string was not in a correct format.
   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
   at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetInstancePort(ConnectionInfo connectionInfo)
   --- End of inner exception stack trace ---
   at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
   at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
   at Spotfire.Dxp.Data.Adapters.Ssas.SsasAdapterConnection.GetDatabases(AdomdOdboConnectionSettings connectionSettings, AdapterCredentialsValue credentials, String[]& databases)
2020-01-22T10:23:35,390-06:00 2020-01-22 16:23:35,390 00udwi0wiLBWVtPjH356 [Main Thread] ERROR Spotfire.Dxp.Forms.Data.Access.AsynchronousTaskManager [(null)] - Background task failed.
The data source does not exist or it does not respond.
External error:
"The connection string is not valid."
which is caused by "Input string was not in a correct format."

Resolution

Some of the ADOMD.NET drivers that are available on the Microsoft's Download Center (eg: https://www.microsoft.com/en-us/download/details.aspx?id=52676) are not configured to parse the server URL of format "asazure://....." but NuGet, which is package manager for Microsoft development platform, has the latest drivers listed that can parse Microsoft Azure Analysis Services URLs correctly. To resolve, you can download and install the latest package from NuGet:

Method 1:

1. Download the latest Microsoft ADOMD.NET from https://www.nuget.org/packages/Microsoft.AnalysisServices.AdomdClient.retail.amd64/18.2.3 by clicking on 'Download package'.

2. Now install the package using the command available under '.NET CLI'.

3. Reopen TIBCO Spotfire Installed Client

User-added image

If dotnet core (which is needed to run the above command) is not installed on the machine, follow method 2 which is replacing the dll as shown below.


Method 2:

1. Download and install SQL_AS_ADOMD.msi from https://www.microsoft.com/en-us/download/details.aspx?id=52676

2. Download the latest Microsoft ADOMD.NET from https://www.nuget.org/packages/Microsoft.AnalysisServices.AdomdClient.retail.amd64/18.2.3 by clicking on 'Download package'.

3. Change the extension of the NuGet package to 'zip' and extract that zip file.

4. Replace 'Microsoft.AnalysisServices.AdomdClient.dll' available under "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.AnalysisServices.AdomdClient\v4.xxxx" with the 'Microsoft.AnalysisServices.AdomdClient.dll' present under "NuGet Folder\lib\netxx"

5. Reopen TIBCO Spotfire Installed Client

Note: 18.2.3 is latest version available currently available on NuGet. Please install the newer version if available while downloading

Issue/Introduction

Some of the ADOMD.NET drivers that are available on the Microsoft's Download center https://www.microsoft.com/en-us/download/default.aspx (eg: https://www.microsoft.com/en-us/download/details.aspx?id=52676) are not configured to parse the server URL of format "asazure://...." but NuGet, which is the package manager for Microsoft development platform, has the latest drivers listed that can parse Azure Analysis Services URL.

Additional Information

Doc: Details on Microsoft SQL Server Analysis Services Connector External: NuGet ADOMD.NET Package (Current version) External: Microsoft ADMOMD.NET for SQL Server 2016