Runing SSAS Tabular Processing Script (TMSL) from SSAS Database Engine

Question

Hello Experts,

In continuation of my previous question

https://learn.microsoft.com/en-us/answers/questions/2076521/processing-monthly-partition-from-sql-job-using-st?source=docs

We have SSAS Tabular model database and SSAS Database engine.

When trying to execute the TMSL script (mentioned in above question) by calling stored procedure or through SQL job(of type Transact SQL - TSQL) from SSAS Database engine giving error.

DECLARE @return_value int

EXEC @return_value = [dbo].[ProcessMonthlyPartition]

SELECT 'Return Value' = @return_value

GO

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near '{'.

which is obvious as Database engine expecting any DDL syntax (select/update) .

Can you guys please help to know how the TMSL scripts can be called from SSAS database engine either by directly calling SP or through the SQL JOb.

Thanks

Prashant Pandey

Answer 1

Hi，PANDEY Prashant

The SQL engine is unable to directly identify TMSL. Consider setting up an SQL job, and within SSMS (SQL Server Management Studio), configure a Linked Server for the SSAS (SQL Server Analysis Services) instance.

Amend the stored procedure to convey the TMSL command to the Linked Server using JSON.

CREATE PROCEDURE ProcessMonthlyPartition
AS
BEGIN
    DECLARE @CurrentMonth NVARCHAR(20)
    DECLARE @PartitionName NVARCHAR(100)
    DECLARE @JsonCommand NVARCHAR(MAX)
    DECLARE @CurrentYear NVARCHAR(4)
    DECLARE @DataBaseName NVARCHAR(100) = 'dev-ssas' 
    DECLARE @TableName NVARCHAR(100) = 'fact_viewing_session_member' 
    SET @CurrentMonth = DATENAME(MONTH, GETDATE())
    SET @CurrentYear = DATEPART(YEAR, GETDATE())
    SET @PartitionName = 'Partition_' + @CurrentMonth + '_' + @CurrentYear
   
    SET @JsonCommand = '{
        "refresh": {
            "type": "automatic",
            "objects": [
                {
                    "database": "' + @DataBaseName + '",
                    "table": "' + @TableName + '",
                    "partition": "' + @PartitionName + '"
                }
            ]
        }
    }'
    EXEC (@JsonCommand) AT SSASTABULAR;  -- linked server name
END