Executing a PowerShell script from SSMS to operate on a database used in a TimeXtender Classic Project

Installing PowerShell 

To install the SqlServer module from the PowerShell Gallery, start a PowerShell session as an administrator.  For details, review this Microsoft documentation.

Scenario: Process a Single table in an Azure Analysis Services Tabular model

Consider the following PowerShell script:

$secpasswd = ConvertTo-SecureString "***Pass***" -AsPlainText -Force;

$mycreds = New-Object System.Management.Automation.PSCredential ('****UID****’, $secpasswd);

Invoke-ProcessTable -TableName "<tablename>" -Database "<DatabaseName>"   -RefreshType Full   -Server "asazure://centralus.asazure.windows.net/<AS_serverName>" -Credential $mycreds

1. Execute the script in a PowerShell command line to verify it works OK. In SQL Server Management Studio, Right-click an on-prem SQL Server database and select the option "Start PowerShell", then copy-paste the above script to execute.  You may see the following output:

   Impact XmlaResults
   ------ -----------
   Microsoft.AnalysisServices.Tabular.ObjectImpact {Microsoft.AnalysisServices.XmlaResult}

2. In SQL Server Management Studio, enable your MDW database to run xp_cmdshell.

   EXEC sp_configure 'show advanced options', 1;
   GO
   Reconfigure;
   GO
   
   EXEC sp_configure 'xp_cmdshell',1
   GO
   Reconfigure
   GO

3. Create a .ps1 file with the PowerShell script and save it as C:\Temp\PS_Script_Proces_Table.ps1
4. In SQL Server Management Studio, invoke the PowerShell script with xp_cmdshell.  It spawns a Windows command shell and executes PowerShell script from the given file.

   xp_cmdshell 'powershell.exe -File C:\Temp\PS_Script_Proces_Table.ps1'

5. Verify in SSMS that the above command executed successfully.  Review table properties in the Azure Analysis Services Tabular model to verify it is refreshed.