A common misunderstanding: VS vs BIDS vs SSMS

I saw many posts on community sites where people are trying to figure out:

• why business intelligence projects cannot be created in VS after installing Visual Studio 2008
• why they can't create Visual Studio projects in BIDS
• why they don't see VB, C# and other Visual Studio project types, if they have a machine with only BIDS installed
• why all the project types are missing from Visual Studio that they had in SSMS, even if they installed SSMS and Visual Studio as well
• etc.

The answer is because these three environments are not the same. However they are built on nearly the same base, they are different because

1. Visual Studio 2008 is a set of development tools.
2. SSMS uses an isolated Visual Studio Shell, including 3 own project types.
3. BIDS uses an integrated Visual Studio Shell, and is able to handle the BI related project types. However these projects can be used in VS2008 as well, if both installed.

SSMS and BIDS are not extensions of Visual Studio. They are separate applications running Visual Studio Shell.

For a better understading and more information on the VS Shell and SSMS:

• Visual Studio Integration SDK: Visual Studio Shell-Based Applications
• SQL Server 2008 R2 Books Online: Files that manage solutions and projects

Regarding BIDS: as of 15th Feb 2012, the Books Online is wrong. It states that BIDS (for SQL Server 2008 and SQL Server 2008 R2) is "Visual Studio 2008 with additional project types".

No, it is not. For those who already read themselves through the links given above, it might be clear.

By the way, BIDS and its BI project types (Reporting Server, Analyis Services, Integration Services and 4 others) can be installed using the SQL Server installation utility. SSMS as well.
A SQL Server Developer Edition costs about $50 per developer.

Hope it helped.

[Update: There is no BIDS for SQL Server 2012. Instead, there is another tool.]

New SSIS Wiki pages for you, and a surprise for me

I have continued to add content to Technet Wiki this week, and I just spot that I'm on the leaderboard. Very funny - it seems I'm more active this week than Ed Price. He must be on holiday or something :)
OK, I have created less popular articles, but hey - he is an Official Wiki Ninja! :) [LOL, nice acronym.]

Anyway, it's not unthinkable that I'll get higher on this list since there is a lot of things to write and link here.
If you're interested in SSIS transformations, and you would like to see short summaries including references to the details, just follow the link.

SSIS Transformations Wiki: Conditional Split

Again, I added a new page to Technet Wiki.

The second page is about the Conditional Split transformation.

Gallery script: List all SSIS packages in MSDB (SQL 2008-2012)

Just uploaded a small contribution to the Technet Gallery. It was created originally by Olaf Helper, however it was working on SQL Server 2005 only.

So this script will list the SSIS packages stored in the MSDB from SQL Server 2008 to 2012, with some additional details:

• Package type
• Owner name
• Encryption
• Date of creation
• Version
• Version comment
• Package size in bytes

This script fails on SQL 2005.
I checked the documentation of SQL 2012, and it is subject to change, but it seems that the system table names for the MSDB package won't be modified.

Analysis Server Properties A-Z: CollationName

The official documentation says...

This property is "A string property that identifies the server collation." according to the documentation.
It is a property which can have a value from the Windows collation list. For those who are familiar only with the SQL engine: Analysis Services supports only Windows collations - no SQL collations are allowed.

How and where it can be set?
In the Language/Collation page of the Analysis Server Properties window:

Analysis Server Properties - Language/Collation page

What happens if the value of this parameter changes?

This change will be applied in the <CollationName> tag of msmdsrv.ini. A change in collation can affect sorting and character comparison. Just for an example, "fruit" and "Fruit" won't be the same if you set a case-sensitive collation (check out this related post of Jamie Thomson).
If you don't specify collation from the client side, then this will be the collation used in the session, which also affects sorting.

Restart needed to apply changes:

No. Your changes are applied immediately.

Category:

N/A. It's a property which is not on the General page.

Default Value:

The value of this property is specified during installation, when Latin1_General_CI_AS is offered by default.

Analysis Server Properties A-Z: AllowedBrowsingFolders

The official documentation says...

As the official documentation says here, the AllowedBrowsingFolders property is "A string property that defines the names of the folders where logs, backups, and other objects are allowed to be created on the server, in the form of a comma-separated list."

But well, it's a pipe-separated list.

How and where it can be set?
In the General page of the Analysis Server Properties window, after the "Show Advanced (All) Properties" checkbox had been set:

This change will be written into the msmdsrv.ini file, in the <AllowedBrowsingFolders> tag.

What happens if the value of this parameter changes?

For example, when you connect to your instance with SSMS, and add a new database, you will be prompted for the storage location of the new database:



Analysis Services New Database dialog

When you click the ellipsis button, a list will appear where you can select the location of the new Analysis Services database:

Browse for Remote Folder dialog

The AllowedBrowsingFolders property controls this list, so only items defined in the Analysis Services Properties window at this property can be seen here.

Restart needed to apply changes:

No. Your changes are applied immediately.

Category:

Advanced

Default Value:

"\data". This is a relative path to your Analyis Services installation folder.

SSIS Transformations Wiki: Union All

I just added a new page to Technet Wiki. I plan to add all transformations of SSIS, however someone might be faster :)

The first page is about the Union All transformation, you can read it here.