Saturday, January 22, 2011

Share Excel Workbooks on SharePoint Server 2010

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 worksheetimage







  • Click on the Definition tab and then click on the Export Connection File button on the bottom  image
  • Save the file on a SharePoint site with Office Database Connection extension (*.odc)
image
  • 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
image
  • Now you could Save and Publish your Workbook to SharePoint site by Click on the Save & Send  menu
image
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

SSIS Error Loading Excel Data with Office 2010 installed

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:
image
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.
There are two ways to resolve this issue:
    1. Install the latest office driver form the following link: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
    2. Save the Excel spreadsheet to be Microsoft Excel 97-2003 (*.xls) format to load the data in.