SQL Server Data Tools, BIDS, Visual Studio – What Do You Need?

The Business Intelligence design components of SQL Server have an identity crisis.

What I’m talking about are the tools that are used by designers to create BI objects in the SQL Server Business Intelligence stack, specifically Analysis Services (SSIS) OLAP cubes and tabular models, Integration Services (SSIS) ETL packages, and Reporting Services (SSRS) reports. These tools have always been bundled into a single product as part of the SQL Server distribution. The original incarnation of the tools was called Business Intelligence Development Studio, or just BIDS. It was originally introduced with SQL Server 2005, and was included on the SQL distribution media as an optional install component.

BIDS was a distribution of the Visual Studio shell, and a set of bundled project types for creating cubes, SSIS packages and SSRS reports. The original version was based on Visual Studio 2005, and subsequent releases of SQL Server stayed in step with more recent versions of Visual Studio. The projects were tied tightly to the Visual studio versions. If, for example you had Visual Studio 2010 already set up, installing BIDS would still install a different Visual Studio to support the projects. BIDS maintained its own identity. Installing BIDS is straightforward, you simply run setup from the SQL Server media, and select “Business Intelligence Development Studio”.

When SQL Server 2012 was released, a change was introduced. In addition to the projects required for tabular models in SSAS being added to the tools, they also received a new name. Henceforth they were to be known as SQL Server Data Tools (SSDT). As expected, the requisite level of Visual Studio was incremented and SSDT was based on Visual Studio 2010. The installation experience didn’t change fundamentally, the installation option just took on the new name.

So far so good. Products get renamed all of the time. Simply substitute SSDT for BIDS, and everything is pretty much as it once was.

SQL Server 2014 is the latest SQL Server version, and it introduced another major change. Data tools is no longer available from the SQL Server installation media.

This is where it gets very confusing. It was always possible to download BIDS or SSDT directly from Microsoft. These products don’t require a license to deploy and use, so they were freely available. This is also true with SQL 2014, but now, a download is the only way to get the tools. That’s simple enough. However, if you search for SQL Server Data Tools and download the version for either Visual Studio 2012 or Visual Studio 2013, you won’t find the projects that you were looking for. You’ll instead find projects for deploying databases and DACPACs. So what’s going on here?

As it turns out, a separate Microsoft team put out a separate set of VS project templates in the SQL Server 2012 timeframe that were also called SQL Server Data tools. Apparently there are a finite quantity of names.

The product that we originally knew as BIDS, and then SSDT was renamed one again for the SQL Server 2014 wave of products. Well, only sort of renamed. It’s now called SQL Server Data Tools – BI. Apparently this was intended to avoid confusion…. SSDT-BI is available for either Visual Studio 2012 or Visual Studio 2013. If you don’t already have a Visual Studio installed, it will install a VS shell for you.

The summary of all this is to say that if you want to build BI projects for SQL Server, you’ll need to have the right tooling for your target server, and that tooling is as follows:

Server Toolset Name Location
SQL Server 2008 R2 and below Business Intelligence Development Studio On SQL Media
SQL Server 2012 and 2012 SP1 SQL Server Data Tools On SQL Media
SQL Server 2014 SQL Server Data Tools – BI Download for Visual Studio 2012
Download for Visual Studio 2013

The complete current Data Tools product set is laid out and can be downloaded from here. The Data Tools team blog is here.

I hope that this helps to clear up some confusion. I can’t wait to see what they have in store for us in V.Next….

Advertisements

23 thoughts on “SQL Server Data Tools, BIDS, Visual Studio – What Do You Need?”

  1. About SQL Server 2016 CTP2 Preview, Is that use SQL Server Data-tools-BI for VS 2012 or 2013 same as SQL Server 2014 right ?

  2. Thanks for lending some clarity to the tool set structural deformities…I installed the SS 2014 developer db, Put the SSAS instance in place with Adventure works etc. Running a few MDX queries just fine. But when I dove into the search for Visual Studio tools SSAS, SSRS, and SSIS I found the dilemma you write about. One thing left unclear is which version of Visual Studio is best installed. I met will trouble down loading the full VS 2013 Pro Update 4 file, as the SDC did not unpack after the down load completed. So I tried the VS 2013 Pro Update 4 Web 32 bit version. That took a while but it did succeed. But I think that must be the newly named version of the tools as all the tools in it were application related like C++, C#, F# and a host of other application builders. My long winded point is that I find it disheartening that Microsoft can’t bring themselves to hire staff that can write clearly and imagine things from the point of view of likely installation difficulties. Their products would be used more widely if they could achieve that minimal clarity.

    Thanks for you article though. I did derive the right tools as a result. (note the ambiguity between running/installing the SSDT – BI tools and thinking you have to have the DB install for BI. as if the tools will not build an SSAS environment in the normal 2014 developer install’s analysis services instance.

  3. Thank you for the useful post. If you need to develop or manage DBs, I can advice you to use dbForge Studio for SQL Server, it’s very convenient and powerful tool on my mind.

  4. Hi,

    I’m hoping someone can help me, it will be much appreciated.

    I recently migrated all SSRS reports from SQL Server 2008 R2 to SQL Server 2014.

    I’ve Installed “Visual Studio Professional 2015 with update 1” followed by the installation of “SSDT December 2015 Preview for Visual Studio 2015” (for the AS, IS and RS designers).

    I then made changes to an existing report in Visual Studio 2015 and previewed it without any issues.

    However the problem occurs when I try to upload the report to the report manager (SQL 2014), it returns the following error;

    The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: The report definition has an invalid target namespace ‘http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition’ which cannot be upgraded. (rsInvalidReportDefinition)

    Any ideas why i’m getting this error and how to get around it?

    Thanks

  5. Thanks for the information. I’ve been trying really hard to find out the differences between BIDS, SSDT and what not. Looked many places and this was the only post that gave me a clear understanding.
    Thanks a ton and Cheers.

  6. Thank you for this information. I have one question regarding SQL version. We have always used SQL Server standard edition for SSRS and BIDS. We are thinking of upgrading our SQL server to 2012 R2 and found that now Microsoft has release an edition called SQL Server Business Intelligence edition. Do you know if we need to get this edition or does Standard edition come with SSDT tool?

    Kind regards,

  7. Thank you for this information. However, I have a question.If I am using SQL Server 2014 and I would want to edit my rptproject using Visual Studio 2010, is this still possible?

  8. I have to agree with Gordon, MS is a real mess sometimes and they are very slow to correct bad situations. Then they shift technologies every 20 minutes so they can get people to waste money on their latest hot technology which often turns out to not be all that hot.

  9. Are the target server and toolset tied up? E.g. if you want to create (or modify) an SSIS package deployed to SQL Server 2012, do you need to use “SQL Server Data Tools” from the “SQL Media”? Or can you just use the latest version (currently “SQL Server Data Tools for Visual Studio 2015”)?

  10. Thanks for the post, I wanted to know when the target is Azure SQL Server (RTM), will SSDT for Visual Studio 2015 work? Thanks!

  11. Even in 2019, Microsoft is really sucking it big time with SSIS. You cleared a lot of things up with this explanation. Thank you!!

  12. Let me rephrase, Visual Studio 2019 does not have an SSDT download, Visual Studio 2017 still does.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.