Wednesday, December 22, 2010

Server 2008 and SSRS

Sadly we do not have a development or test environment for our Reporting Server. However, I wanted to test the upgrade from SQL Server 2008 to SQL Server 2008 R2. I wanted to know how it will affect our reports, most specifically reports that use Dundas controls.

So I asked our server admin to create me a VM for testing. I installed SQL Server 2008 database engine and reporting services, however, I had trouble deploying reports to it. It's been a while since the last time I set up reporting service so I forgot that I had to assign the System Administrator role specifically within reporting services Site Settings section. The problem is that I kept getting the error "The permissions granted to user [] are insufficient for performing this operation. (rsAccessDenied) "

It turns out that the solution was to run IE as Administrator. That made the Settings page available.
Deploy still gave me the same rsAccessDenied error. The solution for that was also to run BIDS as Administrator.

Many thanks to Johan Åhlén's blog where I found the solution.

SSIS - Office 2010 Woes

Good news: I got a new laptop at work that's much faster than my previous one.
Bad news: new laptop = setup issues.

My previous laptop was 32-bit and this one is 64-bit which came with its own challenges. I have the 64-bit Office 2010 installed on it. If you have ever worked with SSIS in a 64-bit environment then you know where this post is going.

I am working on an SSIS package that import data from Excel 2007 to SQL Server. I had created the package on my previous 32-bit laptop and I have to tweak it. Now I get this error: "
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."

I search for a solution and I come across a few things. One is to set my project to use the 32-bit runtime. Another one is to download the Microsoft Access Database Engine 2010 Redistributable which has backwards connectivity components. Another thing suggested changing the connection string to Microsoft.ACE.OLEDB.14.0.

Neither of the above solutions have worked. What eventually worked is downloading and installing the 2007 Office System Driver: Data Connectivity Components. Just for reference this is my connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=2";

I hope this post will help someone facing the same problems. Merry Christmas!

Just a note: in order to install the Access Database Engine 2010 32-bit version I had to uninstall any 4-bit versions of Office.