Hello All,

I am going to share how we can process tabular model from SSIS and sql agent job using powershell script.

Powershell Installation: To connect azure from powershell, we need az and sql server modules to get installed on powershell:

Following are steps need to follow to install modules:

Step 1: [Net.ServicePointManager]::SecurityProtocol = [Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls12

Step 2 : Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted

Step 3: Get-PSRepository

Step 4: Install-Module -Name Az -AllowClobber

Step5 : Install-module SqlServer -Force -Allowclobber

Step 6 : To Check > Get-InstalledModule

Note: Run above commands by opening powershell as administrator.

PowerShell script to process tabular model on AAS:

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 Import-Module SqlServer -Verbose -Force Import-Module Az.Accounts appSecret = ""appSecret=""tenantId = "" appId = ""appId=""password = ConvertTo-SecureString appSecret -AsPlainText -ForceappSecret−AsPlainText−Forcepscredential = New-Object -TypeName System.Management.Automation.PSCredential(appId,appId,password) Connect-AzAccount -ServicePrincipal -Credential $pscredential -Tenant tenantIdtenantIdAppId = Get-AzKeyVaultSecret -VaultName "######" -Name "PBIRefresh-ClientID" -AsPlainText TenantId = Get-AzKeyVaultSecret -VaultName "#####" -Name "PBIResfresh-TenantID" -AsPlainTextTenantId=Get−AzKeyVaultSecret−VaultName"##########"−Name"PBIResfresh−TenantID"−AsPlainTextPlainPWord = Get-AzKeyVaultSecret -VaultName "###########" -Name "PBIRefresh-ClientSecret" -AsPlainText $PWord = ConvertTo-SecureString -String PlainPWord -AsPlainText -ForcePlainPWord−AsPlainText−ForceCredential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $AppId, $PWord Invoke-ProcessASDatabase -Server "##########" -DatabaseName "####" -RefreshType "Full" -Credential $Credential -ServicePrincipal

Note: Azure Key Vault is exceptional. You can use only service principle account to authenticate.

You can save this script in ps1 format and call this file from sql agent job,SSIS package or any other tools.

This would help you to process AAS model automatically after your datamart get loaded on premises server.