How do I add a structured ODBC data source to a tabular model using the AMO tabular library

Question

Adding a structured ODBC data source to my model results in an error.

I want to generate a tabular model on a SQL Analysis Services server with compatibility level 1400 using the Microsoft.AnalysisServices.Tabular library using structured (ie. M / Power Query), non-legacy (ie. ProviderDataSource) data sources. I installed the library using NuGet package Microsoft.AnalysisServices.retail.amd64 (16.3.0).

Here's my data source definition.

                myDatabase.Model.DataSources.Add(new StructuredDataSource()
                {
                    Name = "ODBC",
                    Description = "An structured ODBC data source definition",
                    ConnectionDetails = new ConnectionDetails()
                    {
                        Protocol = DataSourceProtocol.Odbc
                    },
                    Credential = new Credential()
                    {
                        AuthenticationKind = AuthenticationKind.UsernamePassword,
                        EncryptConnection = false,
                        Username = "MYUSERNAME",
                        Password = "MYPASSWORD"
                    }
                }

When I run this code, I get:

COM error: Microsoft.Data.Mashup; The given data source reference is not a valid data source.

It doesn't give me any pointers where to look or what is wrong specifically. I suspected the definition needed a server address, but the address property of the ConnectionDetails object cannot be set according to the documentation.

ConnectionDetails.Address Property

Address of this connection. It can't be set, instead it should be modified directly.

GerardV GerardV · Accepted Answer · 2019-06-23T09:05:48

When reviewing my question and off course further investigating the documentation, I constructed this solution. For those who struggle with the same problem, I figured it'd be nice to post it here.

Credential credential = new Credential()
{
    AuthenticationKind = AuthenticationKind.UsernamePassword,
    EncryptConnection = false,
    Username = "MYUSERNAME",
    Password = "MYPASSWORD" // Please note that this won't persist.
};

ConnectionDetails connectionDetails = new ConnectionDetails("{ 'protocol': 'odbc', 'address': { 'options': { 'dsn': 'MYODBCDSN' } }, 'authentication': null, 'query': null }");

dbWithDataSource.Model.DataSources.Add(new StructuredDataSource()
{
    Name = "ODBC",
    Description = "An ODBC structured (ie. non-legacy) data source definition",
    ConnectionDetails = connectionDetails,
    Credential = credential,
    Options = new DataSourceOptions( "{ 'hierarchicalNavigation': true }" )
}

What I basically did, was pass in a JSON string in the ConnectionDetails constructor, setting the 'read-only' address property as well.

How do I add a structured ODBC data source to a tabular model using the AMO tabular library

1 Answers