Screenshot of the "Installation Type" page in the SQL Server 2012 SP1 setup wizard

Installing SQL Server Data Tools – Business Intelligence for Visual Studio 2012

As if renaming the accurately titled Business Intelligence Development Studio (BIDS) to the rather ambiguous SQL Server Data Tools (SSDT) wasn’t bad enough, in December, Microsoft’s latest SSDT release only brought half the expected capabilities to Visual Studio 2012.  Yep, the December 2012 SSDT download was missing a key component: the project and item templates for developing MS BI projects in Visual Studio.  Thankfully, the newest release (5th March, 2013) has finally added all of the MS BI templates to SSDT, so you can now develop SSIS packages, SSAS cubes and SSRS reports in the Visual Studio 2012 environment.

Which version?

Unfortunately, they’ve not made the whole process easy.  Searching for “SQL Server Data Tools” will likely lead you to a download which, upon installation, will add connectivity and server management tools to VS 2012 – making it like an up-to-date version of SQL Server Management Studio (SSMS), but without the BI project templates.

The latest release (with the BI templates) is actually called:

Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012

So make sure that if you’re trying to get SSDT for BI development work, that you download the correct version.  Unfortunately, that wasn’t the end of the issues, as I had a bit of trouble with installation that I felt needed sharing.

Installation woes

Begin by downloading the correct installer for the BI enabled version of SSDT from http://www.microsoft.com/en-us/download/details.aspx?id=36843 (782 MB).

Screenshot of the "Installation Type" page in the SQL Server 2012 SP1 setup wizard

Fig. 1: On a 64-bit SQL instance, be sure to select “Perform a new installation of SQL Server 2012”

Once you execute it, the installer will unpack and run the SQL Server 2012 SP1 setup wizard.  Don’t worry about this, remember that SSDT, like BIDS before it, is actually a component of SQL Server based upon the Visual Studio shell, NOT actually an extension to Visual Studio itself.

The trick with the installation is when you reach the Installation Type step (see Fig 1.).

If you’re running an x64-based SQL instance (64-bit), make sure to select “New Instance” on the Installation Type page, and NOT “Add features to an existing instance”.

This is because although the SQL Server instance is 64-bit, the Visual Studio 2012 shell is actually 32-bit.  If you attempt to upgrade a 64-bit instance with a 32-bit component, it fails the Installation Rules checks and won’t allow you to proceed.

Choosing “New Instance” will work but don’t worry, it doesn’t actually require creation of a new SQL instance, it just allows the installer to get past the pre-installation checks.

If you’ve got a 32-bit instance of SQL Server, it doesn’t matter what option you choose here.

Installation complete

Screenshot showing adding a new Business Intelligence project in Visual Studio 2012

The new Business Intelligence Project templates in action

Once the installation has completed (may require a restart), you can open Visual Studio 2012 (or the new SQL Server Data Tools 2012 item on your start menu) and get developing.  Click “New Project” in the File menu and check for the “Business Intelligence” templates to confirm that it’s worked.

I’ve yet to find any real differences between the Visual Studio 2012 based SSDT and the Visual Studio 2010 based version that shipped with SQL Server 2012.  At the moment, the main advantage of using this release seems to be to take advantage of the improved features of Visual Studio 2012 over its 2010 counterpart, rather than any advancements in the Business Intelligence templates/tools themselves.

They might be there, however, I just haven’t come across them yet.  Let me know in the comments below if you’ve spotted any improvements over SSDT 2010 and what they are.

Notes from building a Custom ForEach Enumerator in SSIS

I recently posted about a quandary in which I found myself that led to me building my own extended ForEach File Enumerator in SSIS.  All things considered, it was a reasonably straightforward experience, with most of my issues stemming from a relative unfamiliarity with Windows Forms development (I was always an ASP.NET man).  The whole process can actually be split into four very simple steps to make things easier:

  1. Create your Enumeration function
  2. Design your UI for SQL Server Data Tools (SSDT)
  3. Validation and assignment of input from the UI
  4. Deploy your new component.

As long as your new custom component isn’t too complicated, these steps can be completed very quickly, meaning you can be up and running in only a little longer than it would take to write everything in a Script Task, and think of the re-usability! Continue reading “Notes from building a Custom ForEach Enumerator in SSIS” »

C# Code for custom enumerator, overriding the GetEnumerator method

Extending the ForEach File Enumerator in SSIS

One of the primary reasons for using SSIS is to process multiple files, ETL (Extract, Transform, Load) data, and feed it into a destination, for example, a data warehouse. Built-in components such as the For Loop Container and the ForEach Loop Container make it very simple to enumerate a collection and perform some processing tasks for each entry, be it an array, a dataset, or a directory containing files. In fact, the ForEach File Enumerator option of the ForEach Loop Container allows you to even specify a mask to filter the list of files in a directory prior to processing, so you can weed out unwanted files from the loop. But what if a simple string match isn’t powerful enough to perform the filtering you need? Continue reading “Extending the ForEach File Enumerator in SSIS” »

Architecture diagram for the BISM

Microsoft BI 2012: A Year In Review

With the year drawing to a close, I thought it would be the perfect time to recap the major developments in Microsoft Business Intelligence throughout 2012.  Unsurprisingly, the launch of SQL Server 2012 proved to be the focal point of this year’s releases, although we had a few extra surprises along the way: Continue reading “Microsoft BI 2012: A Year In Review” »

Screenshot of MDX Studio containing my expression

Time Format on non-Date Dimensions in SSAS

I ran into a rather strange situation recently while attempting to set up some new calculated measures in an SSAS cube.  I’m still new to MDX, so I ended up chasing my tail around for a while (and crying out for help on both StackOverflow and MSDN) before I eventually tracked down the culprit and solved the issue.  Basically, I was trying to create a calculated measure, which, using a couple of date attributes from one dimension, would apply to my [Time] dimension and filter the value of a specified measure to match.  Not hard, right? Continue reading “Time Format on non-Date Dimensions in SSAS” »