Running SSAS Jobs in JAMS

Follow

How do I Run SSAS in JAMS?

With SQL Server 2012 or newer, JAMS uses PowerShell to call and execute SSAS.
Users with SQL 2005 or 2008 can run SSAS by using a combination of the JAMS Command execution method and the ASCMD Utility.


Run SSAS Jobs in JAMS with SQL Server 2012 or newer

SSAS queries or scripts contained in input files can be run using the Invoke-ASCmd cmdlet in SQL 2012 or newer. Syntax, description, parameters, inputs and outputs, and examples for this cmdlet can be found on Microsoft's help site. The SQLAS or SQLASCMDLETS Powershell Module must be installed before proceeding. Information on how to install these modules can be found at the bottom of this page.

To run SSAS as a JAMS Job, users will need to complete the following:

  1. Create or locate SSAS input files. These will be queries or scripts written in XMLA, MDX, or DMX.(Users with a SQL Server 2016 Analysis Service instance using the Tabular Server Mode may use TMSL)
  2. Use the Invoke-ASCmd cmdlet in PowerShell to test calling the input files. User code may look similar to this example:
    Invoke-ASCmd -InputFile:"C:\MyFolder\DiscoverConnections.xmla" 
  3. Once the code has been verified, create a JAMS Job and select the PowerShell execution method.
  4. Type or paste the Invoke-ASCmd code tested in step 2 into the Source of the new JAMS Job. Finish the JAMS Job setup.
  5. Additional functionality such as processing SSAS cubes are also available in the SQLAS Module and SQLASCMDLETS Module. Microsoft documentation on how to use these can be found here:
    https://docs.microsoft.com/en-us/sql/analysis-services/powershell/analysis-services-powershell-reference

 

Run SSAS Jobs in JAMS with SQL Server 2005 or 2008

Users with SQL Server 2005 or 2008 will use the ASCMD command-line utility to execute input files. Review the ReadMe for the ASCMD Command-Line Utility.

To run SSAS as a JAMS Job, users will need to complete the following:

  1. Ensure ASCMD is operational. Review the readme file above.
  2. Create or locate SSAS input files. These may be XMLA scripts, MDX queries, or DMX statements.
  3. Use the ascmd command in the command line to test running the input files.
  4. Create a JAMS Job and select the Command execution method.
  5. Type or paste the Command execution tested in step 3 into the Source of the new JAMS Job. Finish the JAMS Job setup.

 


 

Having Trouble? Check out these additional tools:

MSSQL Tips to run ASCMD Command Line scrips: 
https://www.mssqltips.com/sqlservertip/2654/using-ascmd-to-run-command-line-scripts-for-sql-server-analysis-services/

MSSQL Tips to automate tasks for Tabular Databases with PowerShell: 
https://www.mssqltips.com/sqlservertip/3415/powershell-commands-for-sql-server-analysis-services-tabular-mode/

PowerShell scripting in Analysis Services: 
https://msdn.microsoft.com/en-us/library/hh213141.aspx

Have more questions? Submit a request

Comments