Saturday, January 22, 2011

You can reuse and share Excel workbooks on SharePoint Server 2010 portals and dashboards by using Excel Services. This means that you can control what data is displayed and maintain a single version of your Excel workbook.
Excel Services supports workbooks that are connected to external data sources. By saving them centrally in a data connection library file on the SharePoint Site, other people could also use the same connection method to view and interact with your data.
To do that you will need to export the data source connection files to SharePoint Site as following:

Open the excel

Go to Data tab and click on the Connection Properties menu for your worksheet

Click on the Definition tab and then click on the Export Connection File button on the bottom

Save the file on a SharePoint site with Office Database Connection extension (*.odc)

Once the data connection file been saved on the SharePoint site, the location of the connection file should be changed and pointed to the SharePoint Site

Now you could Save and Publish your Workbook to SharePoint site by Click on the Save & Send menu

Once you did that, other people from your team/company can view live, interactive workbooks you created by using the browser on the SharePoint site.

Monday, January 10, 2011

Recently I am setting up a virtual machine to play with the new SQL Server “Denali”. The latest Office 2010 applications and Visual Studio 2010 also have been installed on it. I open a SSIS project and create an Excel Connection Manager with default version set to Microsoft Excel 2007 to load a excel spreadsheet that was saved as *.xlsx format .
When I use this newly created Excel Connection Manager as the Data Source in my data flow, I got the following error after click the Preview button:

The SSIS error log has following:

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
People will normally assume that the machine should contains the latest data provider (Office 2007 provider(12)), when the Office 2010 Excel/Access application installed on the machine. Unfortunately, it is not the case.

About Me

I am a Business Intelligence IT professional and currently work for Duke Energy. I enjoy working in BI. This blog was created as a way for me to share some of the challenges and ideas that I have experienced.