.Net Framework versions of custom components with SQL 2008 R2 BIDS

Rather a long title, but I couldn't think of anything shorter. Anyway, it's a topic that I would have preferred was better publicised.

SQL 2008 R2 Business Intelligence Development Studio (BIDS) will only recognise extension components (such as SSIS Data Flow Components) that are built against .Net Framework 3.5. Neither earlier nor later versions will work, and I've yet to find any useful messages to tell you why.

So far I've only done enough testing to find combinations that definitely work, as summarised in the following table.

BIDS Version	.Net Framework version of component
SQL 2005	2.0
SQL 2008	2.0
SQL 2008 R2	3.5

Report Server location and the error: The specified path is not a metabase path

One of the ‘known’ issues with using CRM 4.0 and ESRV0008 is that CRM does not realise that SQL 2008 Reporting Services hosts the ReportServer application outside of IIS. Hence, when you try and edit the ReportServer URL in CRM Deployment Manager, or install CRM, you may get the error "The specified path is not a metabase path". As I said, this is a known issue, and a workaround is described at http://support.microsoft.com/kb/957053 (issue no. 6 in the article).

The problem though is that this workaround doesn’t always, well, work. I tried this, with no success whatsoever. Eventually, I went back to some other issues CRM had had with identifying IIS metadata in the past with CRM 3.0 (e.g. http://support.microsoft.com/kb/916164 ). What I had to do was temporarily change the IIS bindings so that the default web-site (i.e. web-site with ID=1) was on port 80. As long as this is the case when you edit the Report Server URL, then everything works. Fortunately, all I was doing was editing an organisation in an existing deployment, and could arrange downtime to temporarily change web-site bindings; if you’re installing a new CRM deployment it may be harder to play with web-site bindings. For completeness, the steps to make this work were:

1. In IIS Manager, create a new web-site with bindings that match those of the ReportServer URL, as per issue 6 in KB 957053 (link above)
2. In IIS Manager, change the current web-site bindings so that the web-site with ID=1 is on port 80 with no host-headers (if necessary turn off your phone and don’t check email while people try and complain they can’t access their web-sites)
3. In CRM Deployment Manager, disable the organisation, edit the organisation an put in the ReportServer URL you want and continue till the organisation has been updated successfully. Enable the organisation
4. Back in IIS Manager, change the web-site bindings back to how they were, turn your phone back on, and find someone/something else to blame for the temporary outage

Why Microsoft code things assuming the default web-site is on port 80 is beyond me. Oh well.

During my investigations I found out a few more things:

• McAfee anti-virus software hooks into some popular alternate ports (in this case 8081), and it’s not easy to diagnose conflicts when you put ReportServer and McAfee on the same port
• Configuring multiple bindings for the ReportServer URL works fine
• 'All' the above does is make sure that CRM populates the ReportServer with the correct folders, reports, data sources and permissions. It is possible to set all this up manually and get it to work, but it’s very tedious and you may need to change the Guids that identify the reports within either CRM or Report Server

CRM and SQL 2008 Whitepaper

MS have released an interesting white paper about how you can use some of the new SQL 2008 features with CRM - http://www.microsoft.com/downloads/details.aspx?FamilyID=b5bb47a4-5ece-4a2a-a9b5-5435264f627d&DisplayLang=en

The row compression feature, along with the sparse columns, look very useful for reducing the number of pages needed by a table, which is important for ad-hoc advanced finds which use able scans, and pre-filtered reports.

Filtered indexes can be very useful, especially on the statecode for relatively volatile entities. For instance, the ability to use filtered indexes on open cases or opportunities could make a big difference to overall index storage

I need to do some more testing before I'm convinced on the benefits of the page compression. I could see it working for large quantities of relatively static data, such as a large account or contact table, but I think the processing overhead on data modification (which is always hard to quantify) means that I'd be reluctant to use it on more volatile data (e.g. opportunities, cases, activities).

Office 2007 System Driver - errors in SSIS if you don't have them

A quick post, as I didn't find any others out there giving all the details you may need. SQL 2008 SSIS supports the Office 2007 file formats, but requires the Office 2007 System Driver.

If you deploy a package onto a machine without these drivers, you get errors like the following when the component is validated:

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR
The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered
Hresult: 0x80040154 Description: "Class not registered"

Installing the Office 2007 System Driver should fix this.

Report Builder 2.0 for SQL 2008 - No need for a report model

I've always considered the Report Builder tool that ships with SQL Reporting Services to be a good tool for generating reports without SQL knowledge, and in the context of CRM it's much more powerful than the CRM 4.0 Report Wizard.

One drawback of the Report Builder for SQL 2005 was that it needed a Report Model to abstract the underlying data, and Report Models are not dynamic (see below for why this was a pain). One major advantage of SQL 2008 is that it ships with a new version of Report Builder, Report Builder 2.0, that allows direct connections to the SQL database. This will make it a lot more useful in CRM deployments, especially when your schema changes. A good overview of the use of Report Builder 2.0 can be found here.

Why Report Models are a pain with CRM
Earlier, I said 'Report Models are not dynamic' Put another way, if you built your Report Model on your CRM database, then added a new attribute to an entity, you would have to regenerate your Report Model. In itself this wouldn't be too bad, but when you generate a Report Model from a filtered view you end up with a lot of unnecessary attributes, and a fair bit of work to do to provide a sensible set of attributes in the Report Model. To get round this we wrote a set of tools to manipulate the Report Model files directly and automate a lot of the rebuilding. This saved a lot of time, but still required a deployment process to allow reports against new attributes or entities.