I’m trying something a little different with this blog. I have created a new channel over on YouTube, please feel free to search MitchellSQL and subscribe to my channel! 🙂 Instead of writing everything out in this blog post I created a quick 8 minute video. Please watch the video and let me know if you prefer videos or blog post! Thanks for looking!
Recently I was working on a project with @SQLDusty (Dustin Ryan) and we were upgrading some projects from SQL Server 2012 to 2014. One of our script tasks that we were using to dynamically create and process cube partitions was failing. The worse part is that the error message was completely generic so it wasn’t immediately apparent what was wrong. After a little troubleshooting we were able to find the root cause and solve the issue.
Below is the error message we were getting from the SSISDB:
The problem that we had was that our .net reference for Analysis Management Objects was 11.0 (2012) and it needed to be 12.0 (2014).
Here is a screenshot of the .net reference as seen in the solution explorer.
This screenshot of the properties window displays the version number for my .net reference. Also pay close attention to the Description box. This description is what you actually look for and add when adding the Microsoft.AnalysisServices reference to your script task.
So now all we need to do to fix this problem is remove the current reference and add the correct reference back in.
1) From the solution explorer inside your script task component right click on reference and select “Add Reference….”
2) From the new window that opens up select assemblies and then extensions:
3) Finally select Analysis Management Objects 12.0.0.0 as seen below and add it to your references.
This fixed our problem and if you are also going through the upgrade process I hope this helps you as well!
For those of you who attended my webinar on Processing SSAS cubes with SSIS, thank you! For those of you who didn’t the video is still available here: http://pragmaticworks.com/Training/FreeTraining/ViewWebinar/WebinarID/1714.
Below are a few of the questions I received in the webinar. There will be a couple of follow up blogs for the more detailed questions.
Q: Can you please share the script used to generate the partitions?
A: I will post a follow up blog in the next couple of days with step by step instructions. Sharing my exact script would not be helpful. I will update this post with the additional blog.
Q: How are you stopping through your SSIS Steps within package execution?
A: Breakpoints! I used breakpoints to pause the package during execution so I could explain exactly what was going on. To enable breakpoints on a task inside SSIS you simply right click on the task and then select edit breakpoints. I showed this at the end of the webinar during Q&A.
Q: What are our best logging options if we do all of our cube processing with script tasks instead of the native components?
A: Your best logging options are going to be setting up a Profiler Trace or Extended events. I briefly explained setting up a trace at the end of my webinar.
Q: Does the C# script task need the connection string information to query SSAS for the partition information?
A: Yes. In my webinar I showed how to dynamically create partitions for your cube inside of SSIS. Before creating them I used a script task to see if the partition already existed. I have posted a follow up blog below:
Q: How are the aggregation designs handled on the dynamic partitions?
A: This is handled in the XMLA by specifying the Aggregation ID. I showed this at the end of my webinar during the Q&A section and I will also include this in a follow up blog on creating the XMLA for dynamic partitions.
Q: Is this applicable to tabular cube processing?
A: Yes. The analysis services processing task processes tabular models, cubes, dimensions, and mining models.
Once again thank you for attending our free webinar. Until next time!
Mitchellsql 