I am developing a datamart to feed Performance Point dashboards/scorecards. To deal with managers who do not have a database source, I will have them submit an excel 2003 file to a SharePoint document library (by email). the excel file will have a consistent format.
I am developing an SSIS script that will run every monday morning to update the datamart with any new metric data (combined key = MetricID and ReportWeek); ie. every metric has one value for each week...so the script will either add a record or update an existing record as appropriate.
My problem is that I can read an excel file from my desktop, but when I use the path to the excel file on the sharepoint I get an error message:
"Microsoft JET Database Engine - ! Failure creating file."
this is in the Excel Source Editor tool in the Business Intelligence Development Studio.
My SharePoint Admins have added my directory into the 'Shared Services Administration, Excel Services Trusted Data Connection Libraries'...but I seem to still have a connection problem to that sharepoint library.