Performing and Automating Microsoft BI Related Tasks with PowerShell

I just ran into PowerShell somehow. If you have some familiarity in using .NET, then it should be around 2 hours to pick it up. Of course, you will be on the same basic level like me, but these scripts will work as expected :)

I quicly wrote some scripts and shared them in the TechNet Galleries:

• Listing Enabled SQL Agent Jobs Executing SSIS Packages: there was a request from someone to write it, after they could list all their packages in MSDB with an earlier T-SQL script.
• List Elevated Permissions in an Analysis Services Database (PowerShell): this script is nice to have before starting some security audit activities on SSAS databases.
• Listing Not Processed Objects on an SSAS Instance (PowerShell): this is for those who have a lot of databases, and would like to check if something should be processed.
• Bulk Report Deployment with PowerShell: this is useful for those who have a nice report folder structure, and would like to deploy easily.

If you need some changes to the scripts, please let me know on the Q and A sections of these contributions. If you don't need any changes, and the scripts are OK, then please don't forget to rate them :)

T-SQL script for refreshing all views in a database

There are scenarios when SELECT * is used in views - it's not uncommon in a relational data warehouse.

If WITH SCHEMABINDING is not specified when the view is created, then changing the tables behind the views might corrupt the mapping of the table columns to the output columns, which can have a very confusing result.

A stored procedure called sp_refreshview can be called to correct the mappings. But if you don't have time to look for the corrupted one, you can fabricate a script to loop through all of them.

Or, you can download my one and simply run it. I posted a script to TechNet Galleries, which refreshes all views in a database, regardless of their schema.

Basically it's a cursor solution, calling sp_refreshview for each view in the database.

You can find it here.

SSIS Transformations Wiki: Pivot

Again, a new page for TechNet Wiki, now about the Pivot transformation.
I introduced it through a small example.

SSIS Transformations Wiki: Unpivot

A new page to TechNet Wiki, about the Unpivot transformation.
Of course, I updated the List of Transformations page as well to refer to this one.

Sample: Automatically Incrementing Columns in SSIS

There are cases when you don't have a chance to add an auto-incrementing column to a source of a data flow. Sometimes it's not possible (for example, using a flat file source), sometimes it doesn't make a sense (when the data will be resorted or filtered in the data flow later).

I just uploaded a small example to TechNet Gallery how you can add an auto-incrementing column in the middle of a data flow. It's an SSIS 2008 R2 package - if you need another version, let me know via the Gallery. The package adds two columns to the data flow with Script Components - the sample codes are the same, just written in C# and VB.NET as well.

The package also shows how to use a recursive CTE (in the data source) to generate 500 rows of data. You will need to have a tempdb on your localhost, or to edit the connection manager to refer to any accessible SQL 2008 database.

Ratings, comments and questions are always welcome.

SQL Server 2012 exams and certifications

The changes arrived with SQL Server 2012 are reflected in the related exams and certifications as well.
The most important change is that two MCITP-level certifications will be introduced: the Data Platform and the Business Intelligence certifications. Yeah, the administrator and the developer certifications had been merged.

As the official MS Learning site writes, you'll have to pass five exams to achieve a Professional level (MCITP) certification. Nothing had been shared yet regarding the Technology Specialist level. If you already hold an MCITP certification for SQL Server 2008, it will be enough to pass three of them only, as an upgrade. More details will be provided in mid-April. But if you're planning to clear an MCITP exam on SQL Server 2008 - holding an MCTS SQL Server 2008 certification already -, you can save the fee of one exam by choosing the version upgrade path from MCITP 2008 to MCITP 2012.

It's just a guess from me, but since three of five exams are the same on the Data Platform and on the BI track, I presume these 3 will be needed for the MCTS level.

The common exams for both tracks will be:
70-461: Querying Microsoft SQL Server 2012
70-462: Administering Microsoft SQL Server 2012 Databases
70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012

The MCITP level Data Platform exams will be:
70-464: Developing Microsoft SQL Server 2012 Databases
70-465: Designing Database Solutions for Microsoft SQL Server 2012

The MCITP level exams for Business Intelligence will be:
70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

There are some seats announced for a beta exam program, but these are filling up. The exam codes start with 71 in this case.

Good luck!

You can find BIDS features in SQL Server Data Tools for SQL Server 2012

The development environment for business intelligence (and database) solutions - earlier Business Intelligence Development Studio - is called SQL Server Data Tools in SQL Server 2012.
It can be installed using the SQL Server 2012 installation kit.

It's a Visual Studio 2010 Shell in Integrated Mode. Check out my earlier post regarding VS modes, BIDS, and SSMS.

HUG-MSSQL website launched today

Some of you might already know that I organize the Hungarian User Group of Microsoft SQL Server (HUG-MSSQL).

Today we launched its website! There will be blogs, news, and events posted regarding everything about MSSQL and MSBI.

It will be in Hungarian, but some of you already translated this blog with free online tools anyway :)

How to install SQL 2012 on Windows 8

During the weekend, I was playing around with Windows 8 Customer Preview.

First I tried to install SQL Server 2012 RC0 as I had the installer already, but I ran into an issue (missing system CLR types). I decided not to handle this, since SQL Server 2012 RTM is available now. So I tried to install that one instead.

A smaller issue came up again. But Andre Ziegler saved my day with his post in one of the Windows 8 Developer Preview forums.

So, it's better running the script before you start the installation.
You'll have to run cmd as administrator, insert your Windows install disc and type:

dism.exe /online /enable-feature /featurename:NetFX3 /Source:d:\sources\sxs /LimitAccess

Don't forget to check the drive letter of the source and replace it if necessary.

SQL Server 2012 is installed and running now on Windows 8. Thanks Andre :)

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.]