Microsoft SQL Server 2008 R2 setup starts, when you build a Visual Studio 2010 setup project

When you build a Microsoft Visual Studio 2010 setup project a Microsoft SQL Server 2008 R2 setup might start. To solve this problem on a x64 system:

regsvr32.exe /u "C:\Program Files (x86)\Common Files\microsoft shared\MSI Tools\mergemod.dll"
regsvr32.exe "C:\Program Files (x86)\Common Files\microsoft shared\MSI Tools\mergemod.dll"

on a x86 system

regsvr32.exe /u "C:\Program Files\Common Files\microsoft shared\MSI Tools\mergemod.dll"
regsvr32.exe "C:\Program Files\Common Files\microsoft shared\MSI Tools\mergemod.dll"

Solving: Microsoft Office 2010 does not support upgrading from a prerelease version of Microsoft Office 2010

After removing Microsoft Office 2010 beta 1 from my system I could install Microsoft Office 2010 RTM but not Microsoft Visio 2010 RTM or the SharePoint Designer 2010.

The setup errors:

Setup is unable to proceed due to the following error(s):

Microsoft Office 2010 does not support upgrading from a prerelease version of Microsoft Office 2010. You must first uninstall any prerelease versions of Microsoft Office 2010 products and associated technologies. Crrect the issue(s) listed above and re-run setup.

 

clip_image002

 

Solution

Handling empty fields during SSIS CSV import

Just a reminder:

If you have a CSV file containing the data:

DataInColumn1Row1,DataInColumn2Row1,DataInColumn3Row1
,DataInColumn2Row2,DataInColumn3Row2
DataInColumn1Row3,DataInColumn2Row3,
DataInColumn1Row4,,DataInColumn3Row4

and you import the columns using a SSIS package the empty columns will be converted to an empty string (“”) and not NULL.

How to create a windows explorer shortcut on the desktop to a SharePoint document library

If you want to create a shortcut on you’re desktop to a SharePoint document library, follow the steps on: http://www.endusersharepoint.com/2008/02/13/endusersharepointcom-update-on-creating-a-desktop-shortcut-to-a-sharepoint-library/

 

This will create a shortcut with a target formated like: "\\MySharePointSite.MyDomain.nl\DavWWWRoot\Site1\SubSite1\Shared Documents"

  • MySharePointSite.MyDomain.nl = Top-level site collection hostheader
  • DavWWWRoot = Indicates the URL should be opened in the Windows Explorer and not Internet Explorer
  • Site1 = First site beneath the top site collection
  • SubSite1 = First site beneath Site1
  • Shared Documents = Document library name

Remember: Pasting the internet URL (http://MySharePointSite.MyDomain.nl/Site1/SubSite1/Shared%20Documents) directly in the Windows Explorer will open Internet Explorer, so you can use the internet URL to open the document library directly in Windows Explorer, you must use the \\MySharePointSite.MyDomain.nl\DavWWWRoot\Site1\SubSite1\Shared Documents format

 

image

Unit testing SSIS 2008 expressions

In Microsoft SQL Server 2008 Integration Services, you can use expressions, see (Integration Services Expression Concepts, http://technet.microsoft.com/en-us/library/ms141827.aspx) in your dataflow tasks.

For instance, if you want to import a flat file containing 2 columns: FirstName and LastName and your destination table contains only one column: Name, then you can concatenate the two columns by using a SSIS expression: @FirstName + @LastName, this is a very simple example, but when you are creating larger expressions like:

((DATEDIFF("ss",(DT_DBTIMESTAMP)(SUBSTRING(DatumVoormelding,5,2) + "/" + SUBSTRING(DatumVoormelding,7,2) + "/" + SUBSTRING(DatumVoormelding,1,4) + " " + SUBSTRING(TijdstipVoormelding,1,2) + ":" + SUBSTRING(TijdstipVoormelding,3,2) + ":" + SUBSTRING(TijdstipVoormelding,5,2)),(DT_DBTIMESTAMP)(SUBSTRING(DatumSorteerbeslissing,5,2) + "/" + SUBSTRING(DatumSorteerbeslissing,7,2) + "/" + SUBSTRING(DatumSorteerbeslissing,1,4) + " " + SUBSTRING(TijdstipSorteerbeslissing,1,2) + ":" + SUBSTRING(TijdstipSorteerbeslissing,3,2) + ":" + SUBSTRING(TijdstipSorteerbeslissing,5,2)))) > @[User::MinTijdsduurVoorgemeld]) ? 1 : 0

things can get more complicated and then you might want to use a unit test, to test this specific expression.

There are several ways to unit test, a SSIS expression:

  • Converting the SSIS expression to a SSIS Script Task, some calculation can’t be done with SSIS expressions, then you can use a SSIS Script task, this task can be written in C# and the C# code can be unit tested in Visual Studio.
  • Adding a second SSIS project to your solution, that contains SSIS dataflow task for every unit test

If you just want to manually test the expression without running the complete package, you can create a second SSIS Project to your solution and use flat file source, destination and dataflow tasks to unit test only the SSIS expression. In the screen dumps I added a second SSIS project named: Tpp.Miss.Etl.UnitTest

 

image

UnitTest_Voorgemeld_Input.txt
The UnitTest_Voorgemeld_Input.txt contains all the input data needed to unit test the SSIS expression:
20100521,083030,20100521,084030
20100521,,20100521,084030
20100521,083030,20100521,

 

Data flow task
The data flow task contains:

  • Flat File Source (UnitTest_Voorgemeld_Input)
  • Derived Column (contains the SSIS expression)
  • Flat File Destination (UnitTest_Voorgemeld_Output)

 

image

Flat File Source

image

 

Derived Column (contains the SSIS expression)

Because the Derived column is “Added as a new column”, we must add this new column to the output flat file connection and map the derived column to the output column, to store the expression result in the unit test output file.

image

Flat File Destination

The flat file destination, contains one extra column: Voorgemeld

image

Extra column must be mapped:

image

 

 

UnitTest_Voorgemeld_Output.txt

The UnitTest_Voorgemeld_Output.txt contains all the unit test output, after running the unit test package the result will be:

image

In the file UnitTest_Voorgemeld_Output.txt:
20100521,083030,20100521,084030,20100521083030
20100521,,20100521,084030,20100521
20100521,083030,20100521,,20100521083030

 

By storing the unit test data en the unit test package in the same solution as the SSIS project that uses the expression, our continuous integration server can run the complete packages and the individual unit tests in the nightly builds.

 

 

 

 

 

Difference between PowerPivot Field List and the PivotTable Field List in Excel 2010

When you are using PowerPivot in Excel 2010 there are two field lists:

 

PowerPivot Field List
Used for PowerPivot charts and tables
Click on a PowerPivot chart or tables then on the ribbon > PowerPivot tab > Field List

image

 

PivotTable Field List
Used for normal charts and pivot tables
Click on a chart then on the ribbon > PivotChart Tools > Analyze > Field List
Click on a pivot table then on the ribbon > PivotTable Tools > Options > Field List

image

image

 

 

Field Lists

image

Create you’re first Microsoft PowerPivot report based on the Microsoft AdventureWorks sample databases

If you want to get started with PowerPivot, you will need to install Microsoft Office 2010 and a separate Excel Add-in: PowerPivot for Microsoft Excel 

PowerPivot for Microsoft® Excel X86 Executable (32 bit)
PowerPivot for Microsoft® Excel X64 Executable (64 bit)

After installing the Add-in you should see the extra Excel tab “PowerPivot”, in the screen dumps second tab on the right:

image

 

Microsoft SQL Server 2008 R2 AdventureWorks sample databases

If you want to use the Microsoft SQL Server 2008 R2 AdventureWorks sample databases as source for you’re PowerPivot reports, you will need to install the sample database on you’re database server. The Microsoft SQL Server 2008 R2 AdventureWorks sample databases can be found here: http://msftdbprodsamples.codeplex.com/ 

Before installing the sample database, make sure you’re Microsoft SQL Server 2008 R2 instance has FILESTREAM enabled and is running the SQL Full-text Filter Daemon Launcher service. To enable both feature, follow the steps on: http://msftdbprodsamples.codeplex.com/wikipage?title=Database%20Prerequisites%20for%20SQL%20Server%202008R2

 

PowerPivot tab > PowerPivot Window

image

Home > From Database > From SQL Server

image

Enter servername and databasename (AdventureWorksDW2008R2)

image

Select from a list of tables and views to choose the data to import

image

Select fact table “FactInternetSales” > Select Related Tables

image

On my virtual machine the 80.000 rows where imported in 10 seconds.

You can set friendly names for tables to show in the user interface.

image

During the import the PowerPivot add-in automatically detected relationships

Design > Manage Relationships

image

Add, edit or delete relationships 

image 

PivotTable > Chart and Table (Vertical)

image

Select “Existing Worksheet”

 

image

This will add a chart and a PowerPivot table to sheet1. It will also create a “data sheet“

Rename Excel sheet tabs

image

Click on the chart or the pivottable to show the “Field List”, if the “Field List” does not show click PowerPivot tab and click “Field List”.

image

Drag and drop the EnglishProductname to the “Axis Fields”

Drag an drop the SalesAmount to the Values (default this will calculate the sum of the SalesAmount, but you can change this to Count, Min, Max or Average, by right clicking the Measure SalesAmount and choose Edit Measures…

image

Drag and drop the WeekNumberOfYear to the “Slicers Horizontal”

image

Result: you’re first PowerPivot chart!!!

How to enable https on Microsoft SharePoint Server 2010 RTM by configuring alternate access mappings

Open the Microsoft SharePoint Server 2010 Central Administration > System Settings > Configure alternate access mappings:

 

image

 

Add Internal URLs

image

Enter https://youreservername and choose intranet zone, click on ok

image

Result

image

Start inetmgr right click you’re site and click on Edit bindings…

image

Click Add..

image

 

Choose the server generated certificate if you don’t want to by a certificate, but then you will get an warning when accessing you’re SharePoint site.

 

image

 

Click OK and click on Brows *:443 (https)

image

The warning page

image

 

Click on Continue to this website (not recommended).

image

 

Followed the steps on http://blogs.msdn.com/sowmyancs/archive/2010/02/12/how-to-enable-ssl-on-a-sharepoint-web-application.aspx for more information on using hostheaders (dns entries) and ssl certificates.

Install a development Microsoft SharePoint 2010 RTM Server with PowerPivot and Microsoft SQL Server R2 Reporting Services integration

These are the steps I followed to install a development Microsoft SharePoint 2010 server with PowerPivot and Reporting Services integration.

 

I installed Microsoft SharePoint Server 2010 by following the steps on:

http://powerpivot-info.com/post/66-step-by-step-guide-on-installing-powerpivot-for-sharepoint

 

Then I followed the steps on: http://technet.microsoft.com/en-us/library/bb326356(SQL.105).aspx and http://msdn.microsoft.com/en-us/library/aa905871(SQL.105).aspx to enable Microsoft SQL Server 2008 R2 Reporting Services integration for Microsoft SharePoint Server 2010.

After following these steps, I was able to publish PowerPivot excel reports and Microsoft SQL Server 2008 R2 Reporting Services reports to one top level site collection on Microsoft SharePoint Server 2010. See screen dumps:

 

Start Microsoft SQL Server 2008 R2 setup > Installation > New installation or add features to an existing installation

image

New installation or add shared features

image

SQL Server Feature Installation

image

Reporting Services

image

Instance Configuration

image

Server Configuration

image

 

 

 

Start Reporting Services Configuration Manager

image

Choose Report Server Instance

image

Create a new report server database

image

Report Server Database Configuration Wizard

image

Report Server Mode > SharePoint Integrated Mode

image

Additional actions in Reporting Services Configuration Manager

Web Service URL > Apply

image

Report Manager URL > Apply

image

 

The SharePoint 2010 pre-requisite installer now automatically guides you to download and install the Reporting Services 2008 R2 for SharePoint 2010 Add-in.  Btw, you can download the Reporting Services add-in manually as well. In most cases you can just activate the Report Server Feature in SharePoint Central Administration (http://technet.microsoft.com/en-us/library/bb677366(SQL.105).aspx). In some case you must first download and install the add-in manually, in my case I re-configured the Reporting Services 2008 R2 for SharePoint 2010 Add-in and then activated the Report Server feature on the Central Administration site.

Reconfigure the Microsoft SQL Server 2008 R2 Reporting Services Add-in > Start > Control Panel > Programs and Features > Right click SQL 2008 R2 Reporting Services SharePoint 2010 Add-in > Configure

image

Activate Report Server Integration Feature and Report Server Central Administration Feature > Central Administration > Site Actions > Site Settings > Site Collection features

image

Configure Reporting Services Integration > Central Administration > General Application Settings > Reporting Services Integration

image

Reporting Services > Reporting Services Integration

image

Reporting Services > Set server defaults

image

 

Create a Report Library > Site Actions > More Options > Report Library

image

Enable Report Server Content Types

http://technet.microsoft.com/en-us/library/bb326289(SQL.105).aspx

 

Create a Report > Report Library > Documents > New Report Builder Report (Click once deployment Report Builder 3.0 should start)

image

 

Run click once deployment Report Builder 3.0 (78.2 MB)

image

Create you’re first report

image

 

The Microsoft SQL Server 2008 R2 sample databases can be found at: http://sqlserversamples.codeplex.com/ (82.5 MB). At this moment the November CTP sample database are the last version. RTM sample database should be released soon.

 

Save the report to SharePoint 2010

image

Open report in Microsoft SharePoint 2010

image

Result full screen

image

Result as webpart

image

Links on Managed self-service BI – PowerPivot and Corporate BI

Some useful links on Managed self-service BI – PowerPivot and Corporate BI:

General

http://msdn.microsoft.com/en-us/library/ff628113.aspx

http://powerpivotfaq.com

http://powerpivotpro.com

http://powerpivot-info.com

 

DAX and time intelligence functions

http://blogs.msdn.com/powerpivot/archive/2010/04/12/time-intelligence-functions-in-dax.aspx

http://blogs.msdn.com/powerpivot/archive/2010/04/05/dax-data-analysis-expressions-measures-in-powerpivot.aspx

http://powerpivot-info.com/post/56-introducing-powerpivot-dax-measures

 

Datasets

http://pinpoint.microsoft.com/en-US/Dallas

http://developer.netflix.com/

 

Kasper de Jonge (colleague)

http://www.powerpivotblog.nl

 

SAP as datasource (SAP extractor)

 http://www.theobald-software.com/en/products/xtractppv.htm

 

Webinars

http://www.sqlpass.org/24hours/2010/

 

Virus scanner for Microsoft SharePoint 2010

http://www.microsoft.com/forefront/sharepoint/en/us/default.aspx

In Dutch
Self-service BI in soccer terms: http://www.computable.nl/artikel/ict_topics/beheer/2568712/1277800/scoren-met-business-intelligence-competence-center.html

Cubes
Create you’re first cube and benefits: http://technet.microsoft.com/en-us/magazine/ee677579.aspx