BIDS Integration Story in R2 – The Good, the Bad, and the Ugly

There was a huge discussion thread about the BIDS-Visual Studio integration story in the SQL Server 2008 timeframe where customers complained that BIDS got married with Visual Studio. This required switching both BI and code projects at the same if you want to have a solution with both project types. What has changed in R2?

The good – Report Server projects in BIDS R2 can target either SQL Server 2008 or R2, as explained in this blog. There is no change to Analysis Services projects. If you have a Report Server project with RDL 2008 files, the BIDS Report Designer won’t upgrade the report definitions. However, the moment you add a R2-specific feature, such as the new map region, BIDS will upgrade the report definition to R2 RDL. Therefore, if you want to play with R2 on your machine while the rest of the team is still on 2008 and you are careful, you can install R2 and upgrade BIDS without affecting the rest.

The bad – The R2 Report Server project format has changed to accommodate the new project deployment settings which are explained in the above-mentioned blog. The first time you open a Report Server project in BIDS R2, you will be asked to upgrade the project (not the RDLs). But what if you are forging new frontiers in your company (aka not following corporate standards) and decide to move to R2 while your peers are still on SQL 2008 assuming you use source control and share project files? No worries. BIDS 2008 will automatically strip out the elements in the report project file it doesn’t recognize. But when they save the project the project file will revert to the 2008 format, so you’ll get prompted again to upgrade the next time you load the project in BIDS. To solve this problem, I use a local project file.

The ugly – R2 doesn’t integrate with Visual Studio 2010 as it targets VS 2008 only. And since BI project types are part of BIDS and not Visual Studio, VS 2010 users won’t get the BI project templates either. In other words, you must have BIDS R2 and Visual Studio 2010 SxS. Consequently, you won’t be able to have a solution that spans both Visual Studio 2010 code projects and BI projects. This is like having one TV to watch US channels and a second TV to watch Euro.

It will be nice if one day the SQL Server setup program detects the Visual Studio version installed and ask you which version you want to integrate with. But given that Visual Studio project formats change between releases this may remain a wishful thinking.