THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Microsoft Data Platform MVP from the Netherlands

Process Azure Analysis Services databases from Azure Automation

You can read this blog post at this link: https://jorgklein.com/2017/02/02/process-azure-analysis-services-databases-from-azure-automation

This blog has moved to www.jorgklein.com There will be no further posts on sqlblog.com. Please update your feeds accordingly.

You can follow me on twitter: http://twitter.com/jorg__klein

Published Friday, January 27, 2017 2:32 PM by jorg

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ryan said:

I've tried installing both the 16.5.3 and the 17RC1 versions of SSMS and while I get the "C:\Program Files (x86)\Microsoft SQL Server\140\Tools" directory, there is no "PowerShell" directory

January 31, 2017 5:41 PM
 

jorg said:

Hi Ryan,

If you search your computer for file "SQLASCMDLETS.PSD1", do you find it in some folder with "140" in the path? If you do, you can take the files from there.

February 1, 2017 4:20 AM
 

Ryan said:

Ahh...oops... I was looking in "Program Files" instead of "Program Files (x86)", sorry!

February 1, 2017 2:36 PM
 

Jeff said:

This is great. Detailed and easy to follow. Thanks!

February 1, 2017 3:58 PM
 

JC said:

I try to import the module but I get his "Activity Extraction Failed" message:

Error extracting the activities from module SQLASCMDLETS. Extraction failed with the following error: Orchestrator.Shared.AsyncModuleImport.ModuleImportException: While importing the module, an error occurred while processing the module content. Internal error message: Could not load file or assembly 'Microsoft.AnalysisServices.Core, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified..

Any idea why?

February 1, 2017 5:25 PM
 

JC said:

Never mind, I solved that problem. Still struggling getting the cmdlet to work. something to do with a ConnectionException.

February 2, 2017 1:07 AM
 

Bhavin said:

I installed new SSMS from above mentioned link but cant find 140 directory under Microsoft SQL Server in Program Files(x86) as well as Program Files.

February 2, 2017 1:23 AM
 

JC said:

I wanted to say thanks Jorg. I got this to work great.  The first problem I had was getting the right files for the module.  With the various versions of SSMS I had running I was including files from different versions.  I narrowed that problem and all the files I used had the same date. Once I got through that my issue was entirely in a typo of mine.  Anyway, Thanks!

February 2, 2017 11:45 AM
 

RB said:

This thing works only problem i am facing is script once executed in Azure is not ending model gets refreshed but script is still on going. Can anyone help me for this?

February 3, 2017 2:18 AM
 

Pawel said:

Great article, works like a charm! the only thing is that location of the DLLs changed to C:\Windows\Microsoft.NET\assembly\GAC_MSIL

Thanks!

February 10, 2017 1:21 AM
 

Johann said:

I had the same problem as JC, I solved it by installed the latest SQL Server Data Tools: https://docs.microsoft.com/en-us/sql/ssdt/sql-server-data-tools-ssdt-release-candidate

The assemblies were then available from the GAC: C:\Windows\Microsoft.NET\assembly\GAC_MSIL

February 20, 2017 8:46 AM
 

Aravind said:

I could get the dll's from C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLASCMDLETS and C:\Windows\Microsoft.NET\assembly\GAC_MSIL

But strange part is when i add module to Azure automation, Extracting activities fail with below error.

Error extracting the activities from module SQLASCMDLETS. Extraction failed with the following error: Orchestrator.Shared.AsyncModuleImport.ModuleImportException:

While importing the module, an error occurred while processing the module content. Internal error message:

Could not load file or assembly 'Microsoft.AnalysisServices.Core, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified..

Can any one help?

March 1, 2017 6:07 PM
 

Aravind said:

I got it fixed with other set of assemblies available in  C:\Windows\Microsoft.NET\assembly\GAC_MSIL . Thx.

March 1, 2017 6:45 PM
 

Jeff Dude said:

This article was extremely helpful for automating our cube processing in Azure. There isn't a lot of helpful resources out right now, but this was exactly what we needed. To fit our use case, I had to change the command to Invoke-ASCmd and execute a TMSL query on the tabular model.

March 24, 2017 2:45 PM
 

Bowie said:

I try to import the module but I get the following error:

"Error extracting the activities from module SQLASCMDLETS. Extraction failed with the following error: Orchestrator.Shared.AsyncModuleImport.ModuleImportException: Failure processing module import runbook while getting module content. Error: End of Central Directory record could not be found.."

Has any one a idea?

thx!

April 24, 2017 10:00 AM
 

Chris T said:

Excellent blog, this is exactly what i needed in order to get some scripting working for automated Azure AS deployment.

Cheers!

April 26, 2017 12:10 PM
 

Aravind said:

Hi Jorg,

I am able to process my cube using the above said setup. Would like to know how to send the output of runbook to email in case of a failure ?

May 16, 2017 10:02 PM
 

Preeti Patki said:

I did not get the Microsoft.AnalysisServices.PowerShell.Cmdlets.dll

SQLASCMDLETS.PSD1 files in SQLASCMDLETS fiolder

June 5, 2017 2:09 AM
 

Artur said:

Hi,

I got the runbook running and it does not returns while the model has been successfully completed. Any ideas?

June 22, 2017 3:09 AM
 

Murray Foxcroft said:

you wont find the PowerShell CMDLETS bundled with SSMS 17.1+. The SQLASCMDLETS are now part of the SqlServer module in the PowerShell Gallery https://www.powershellgallery.com/packages/SqlServer/21.0.17099

July 21, 2017 9:02 AM
 

Michał said:

Hey,

I did follow all the steps you mentioned, however I always end up with this error:

Invoke-ProcessTable : Exception has been thrown by the target of an invocation.

At line:25 char:1

+ Invoke-ProcessTable -Credential $AzureCred -DatabaseName DBNAME -R ...

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   + CategoryInfo          : NotSpecified: (:) [Invoke-ProcessTable], TargetInvocationException

   + FullyQualifiedErrorId :

System.Reflection.TargetInvocationException,Microsoft.AnalysisServices.PowerShell.Cmdlets.ProcessTable

Do you have any idea on what it may cause?

July 28, 2017 8:03 AM
 

Nupur said:

When i tried testing the runbook it gave me this error :

Invoke-ProcessASDatabase : A connection cannot be made. Ensure that the server is running.

At line:5 char:1

+ Invoke-ProcessASDatabase -databasename "MultidimensionalProject2" -se ...

August 10, 2017 8:09 PM

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft Data Platform MVP from the Netherlands.
Privacy Statement