Category Archives: Visual Studio

Using the Analysis Services Execute DDL Task

My grandfather used to say, “There’s more than one way to skin a cat.”  I always thought it was a disgusting saying, I mean who would ever want to “skin a cat”?!  Gross!  But the underlying meaning was clear, there’s always more than one way to do something.  And no my grandfather never actually skinned any cats, he was just a country boy from a small town in Iowa, where you quite often had to come up with creative ways to get things done.

There are often times when we get so focused on what we are doing, we forget to look around.  This happened to me a few weeks ago.  We were in the middle of a big upgrade at work, not only where we upgrading our ERP system, we were changing platforms as well (read this as:  no one got more than 3-4 hours of sleep per night for about two weeks, including myself).  We also have a BI solution that uses SQL Server Integration Services (SSIS) to pull data from our ERP system, stores it in a data warehouse on SQL Server then has a multi-dimensional SQL Server Analysis Services (SSAS) cube that sits on top of the warehouse.  Pretty common scenario.

Last part of our ETL process is to process the Dimensions and Facts for the newly loaded data.  I have always used the Analysis Services Process Task in SSIS to accomplish this.  Works great, it’s stable and easy to use.  Just click a few items and you are off and running.

My ETL code had been stable for so long, I hadn’t needed to make any changes since I upgraded from Visual Studio 2015 to Visual Studio 2017.  Little did I know that there was a bug in Visual Studio 2017/SSDT 15.5.2 that will not allow you to use the Analysis Services Process Task if your target version of SSIS is 2016.  You will get a couple of different errors depending on what you are doing.  If you are trying to modify an existing package that uses an Analysis Services Process Task and your target version of SSIS is 2016 you will get the following error:

Post71-EditExistingError

If you try to create a new package using an Analysis Services Process Task and your target version of SSIS is 2016 you will get the following error after it allows you to configure your processing tasks and you click OK:

Post71-NewPackageError

In both of these scenarios you will not be able to save the package.  So what the heck are you supposed to do?!  Here’s where my tunnel vision (and panic) sets in.  How was I supposed to get my SSAS objects processed?

I could always script out my processing tasks using SSMS and drop them in a SQL Agent job step.  But I have multiple environments and multiple cubes so each one would have to be hard coded.  Not a great idea, so scratch that.

Post71-ScriptProcesinSSMS

I could just create a new SSIS project, targeting SSIS 2014, just for the processing tasks.  (Thanks to Teo Lachev for the idea.  And yes, you can deploy SSIS 2014 targeted packages/projects to SSIS 2016.)  It’s better than my first idea.  I could parameterize all the connection managers so nothing would be hard coded, but now my code lives in two different projects.  Not ideal, but it would definitely be better.

Then Gerhard Brueckl pointed me to the Analysis Services Execute DDL Task.

Post71-ASDDLTask

I did some quick research on it here and turns out, this was exactly what I needed to “skin this cat”.  It would allow me to keep my SSAS processing packages in the same project as the rest of the ETL and I could parameterize it to my hearts content (queue beams of light from above and singing angels).  Why hadn’t I thought of this before?!  To be honest I had never paid attention to this task.  It had the term DDL in it, and I wasn’t writing DDL, so why would I ever need it, especially since I was dealing with SSAS.  Yes, I know, need I remind you about my lack of sleep and my tunnel vision.

Post71-ASDDLTaskOptions

The cool thing about the Analysis Services Execute DDL Task is that you have a choice when it comes to your source for the DDL.  I opted for the variable because I needed to parameterize it with the SSAS database name.

Post71-SourceTypes

I was able to script my processing tasks, dump that code into a variable that I built with expressions so that it could be parameterized.  Now one thing to keep in mind is that the XMLA that is produced for SSAS processing tasks has double quotes in it.  You will have to either a) escape the double quotes or b) create a variable for the double quote to use in the XMLA in your expression to build your variable.  I opted for b, create a variable to hold the double quote, it seemed cleaner to me.  Here’s a good post on StackOverFlow on how to do both.

As you can see below, it looks very intimidating with all the XMLA & expressions, but it creates a very pretty XMLA processing task in the end.

Post71-VariableExpression

I created one task for processing dimensions and one task for processing facts.  Mostly because I want to be able to track performance respectively, but you could create one task to do it all.

I was able to add these new packages to my SSIS project and deploy it without issue.  They’ve been running ever since with no problems.

I hope this post helps someone else break out of their tunnel vision.

PSA:  No cats were harmed in the writing of this blog post.

Visual Studio 2017 Irritants From the SSIS Developer View

I was among the many Integration Services (SSIS)/Database (DB) developers out there that cheered when Microsoft finally announced support for SSIS in Visual Studio 2017.  I mean really, VS2017 was released back on March 7, 2017, but if you wanted to develop any SSIS packages in VS2017, you had to wait for the release of SSDT on August 23, 2017.  That’s more than 5 months.  All the other kids got to play with the shiny new toy right away, but we had to wait.  I know there are a ton of reasons for this, but it still sucks.

That being said, I was very happy.  I could finally do ALL my development in the same tool: DB, SSAS, SSRS & SSIS.  But after only 30 days of using it, I have discovered a few things that just irritate me.  I always thought of myself as open to change, but maybe I’m not as open to change as I thought I was.

1 – ISO Is No Longer An Option

That’s right, you can’t just download an ISO and stick it on your network, you have to essentially create your own if you need to do an offline install.  As the title of this post says, this is an “Irritant”, not a deal breaker, but definitely an irritant.

2 – Start Page – It’s Baaack!

There is NO WAY to turn this annoying page off once you close a solution.  You can turn the Start Page off when you first open VS2017 (Tools | Options| Environment | Startup), but as soon as you close a solution, it shows up – ARGH!  See this thread, I’m not the only one annoyed by this behavior.  I’m a very visual person and seeing a list of projects with really long paths don’t help me.  I need my Source Control Explorer to make sure I’m opening the right solution in the right branch.

VS2017-Tools-Startup

3 – Work Items Open in Browser By Default

One of the cool things about using VS as your IDE is that it has this great add-in for source control, especially if you use VSTS, it’s almost a seamless integration (and everyone IS using some kind of source control, right?!).  You can open your list of User Stories based on iteration.  Once you have your list of User Stories, all you have to do is double click the one you want and it opens right there in your VS environment.  Well, the default behavior for where your work items has changed.  Your work items no longer open in VS, they open in a web browser.  You can change this default behavior back to opening in VS (Tools | Options | Work Items | General), but this behavior will be deprecated in the next major version of the product.  There is nothing more irritating than having to switch back and forth between applications when doing development work.  Pro Tip:  If you want folks to use Source Control, make it easy and all in ONE location!!!

VS2017-Tools-Options-WorkItems

VS2017-WorkItems-Deprecated

4 – With Each New Update, Something Breaks

Okay, this is not new behavior in VS2017, unfortunately, it’s continuing behavior from previous versions of VS. <sigh>  I was really hoping that this behavior would stop, but sadly it still persists.  For example, since SSMS is now based on the VS Shell, if I update VS, it usually horks up (yes, that is a very technical term) the settings in my SSMS.

5 – Closing Solutions Take FOREVER!

Holy cow, in previous versions of VS, all I had to do was click File | Close Solution and in a split second my solution would close.  Now, it takes forever.  I have a solution with two projects in it and when I click File | Close Solution it takes anywhere from 15-30 seconds to close (I feel really bad for those that have more than 2 projects in their solution).  Not only that, it says it’s unloading 3 projects, not the 2 that I actually have!  WTH?  According to this thread, the problem has been fixed, but I can confirm that it most definitely has NOT been fixed as of version 15.3.5.  Maybe they only fixed it for Non-SSDT project types?

VS2017-UnloadSolution

Okay, my rant is over, nothing to see here, move along.

How to Exclude Entire Sections in VS Schema Compare

Right now I am so excited, and a little embarrassed.  After using Visual Studio (VS) for database projects for the last 7 years, I am just now finding out about this feature?!  What feature is that, you ask?  Let me tell you…

When using VS for database projects I typically use my environment specific Publish xml file to deploy changes to my local database when experimenting with code changes.  However, every once in a while I will have to use the New Schema Compare tool from the Tools | SQL Server menu when I have a “one-off” database that I need to synch to my database project.

Quite frequently because these are one-off databases there will be a ton of junk items that are in the database, but not in my database project or lots of objects in my project that aren’t in my database.  Either way I want to ignore those changes.  In the past, I’ve always manually unchecked each item, tedious when you have more than two items to uncheck.

SQLSchemaCompare2

I always thought there should be a better way than having to manually uncheck each object, but never thought more about or how to do it.

Well, today I figured it out.  All you have to do is right click on the section and ta-da, you can Include or Exclude all objects depending on the existing state of the objects.  So in the image below, I right click on Delete, select Exclude

SQLSchemaCompare3

and I’m left with the following:

SQLSchemaCompare4

In just one click instead of 14 (if I wanted to exclude all the table drops).

Like I said, I have no idea why it took me so long to figure this out, but surely I can’t be the only person who didn’t know this, so now you do too.

Aggregation Design is Back!

If you use SQL Server Data Tools (SSDT) and SQL Server Data Tools – BI (SSDT-BI) for your SQL Server 2012 development, then you have no doubt been frustrated, like me, by the fact that if you have both of these installed you no longer have the ability to create new Partitions and AggregationDesigns when working with the SSAS MOLAP model.   You can find others that have run into this issue here.

The solution I found was to install both SSDT & SSDT-BI on my laptop then have a VM with just SSDT-BI on it. That way when I needed to work on Partitions or Aggregation Designs (which is very infrequently), I just fire up the VM and I’m off and running.

Well, with SQL Server 2016 development we get to use Visual Studio 2015 and SSDT is now included in that install (although you do not get the BI project types, more on that here), no more do you have to have separate machines. I tested CTP 3 and Partitions and Aggregation Designs work once again. Hooray!

Aggregation Design

One Tool to Rule Them All – Almost

There we so many cool announcements at the PASS Summit this year, but one of my favorites was the “One Tool to Rule Them All”. The SQL Server Data Tools (SSDT) teams and the Visual Studio (VS) team have finally teamed up together to give us one tool to do all our development work for Databases, SSIS, SSAS & SSRS. No more will we have to install Visual Studio Shell, SSDT, SSDT-BI and for those source control minded folks (which should be everyone!) that use Team Foundation Server (TFS), Team Explorer. For SQL Server 2016 development we can do one install of Visual Studio 2015 and call it a day, well, almost.

SSDT Install

I was so excited when I got back from Summit, I downloaded SSDT (CTP3) from here. I was so happy to see the install screen.

SSDT Install Screen

There they were, in all their glory, all the SQL Server project types that I needed. No more having to download multiple install files. Oh happy day!

After the install completed, I was a bit dismayed to discover that it took 3GB of disk space to do this install but I guess that’s par for the course any more.

Visual Studio Install

Next I wanted to see if you got all these same project types with an install of Visual Studio. They announced at Summit that “SSDT” would now be “included” with Visual Studio. So I went out and downloaded Visual Studio (CTP3, Community Edition, i.e., free) from here. And look what shows up on the install features list, there it is in black and white, Microsoft SQL Server Data Tools, almost too good to be true.

Visual Studio Features

Well, we all know that if something seems too good to be true, then it usually is. This is no exception.  Let’s see if you can pick out the reason for my disappointment in the picture below.

Visual Studio Project Types

That’s right, the only SQL Server project types that are installed with Visual Studio are database projects. No SSIS, no SSAS & no SSRS. That was very disappointing. Also note that it installed the templates for Visual C#, Visual Basic, etc., when the only feature that I requested to be installed was SQL Server Data Tools. I guess that’s why this install took 5GB of disk space as opposed to the 3GB that SSDT required.

The good thing about the new Visual Studio is that if you use TFS as your source control, you no longer have to download the separate TFS Team Explorer, it is now built in to Visual Studio. No additional installs are required.

Visual Studio Team Menu

Right “out of the box”, you get the Team menu item. However, this is NOT included in the SSDT install. I guess someone thinks we don’t really need to source control our SQL Server projects <sigh>.

Almost One Tool

Because I use TFS as my source control, I still have to do two installs, SSDT to get ALL the SQL Server project types AND Visual Studio so I can add all my SQL Server project types to source control.

This is definitely better than what we have to do now if we are doing development work prior to SQL Server 2016, but it’s not “One Tool to Rule Them All” yet. I’m hoping that since this is a CTP, the final products will contain “all the things”, but I certainly won’t hold my breath.

Now I’m off to test if they’ve overcome the issue of database projects playing nicely with SSAS projects. For those that use the multidimensional model with partitioning, you know exactly what I’m talking about. I’ll keep you posted with my results.