How to import data from ms sql to tabular model using .NET

Question

So is it possible to import data from ms sql to tabular model ssas using .net library adom.net? For now I know it is possible using wizard of data import, but maybe there is possible to do it without using wizard, but making with .net?

Answer 1

I'm not sure whether you are trying to do a data refresh or add a whole new datasource to your model. However, either one is fairly straightforward with the .net libraries.

    static void Main(string[] args)
    {
        Server server = new Server();

        server.Connect("Data source=YourSSASServerName;Timeout=7200000;Integrated Security=SSPI");

        Database database = server.Databases.FindByName("YourCubeDBName");

        //
        //process database (load in fresh data from SQL)
        //
        database.Process(ProcessType.ProcessFull);





        //
        // add new data source to model (SQL server)
        //
        database.Model.DataSources.Add(new ProviderDataSource()
        {
            Name = "SQL Server Data Source Example",
            Description = "A data source definition that uses explicit Windows credentials for authentication against SQL Server.",
            ConnectionString = "Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;Persist Security Info=false",
            ImpersonationMode = Microsoft.AnalysisServices.Tabular.ImpersonationMode.ImpersonateAccount,
            Account = @".\Administrator",
            Password = "P@ssw0rd",
        });


        // 
        // Add the new database object to the server's  
        // Databases connection and submit the changes 
        // with full expansion to the server. 
        // 
        server.Databases.Add(database);
        database.Update(UpdateOptions.ExpandFull);
    }