A year ago, I wrote a blog post to caution SSIS users that the ACE Provider and its Excel driver were not supported by Microsoft for use on the server.
That position has changed - in certain circumstances! - as I learned today from SSIS dev manager David Noor. The download page for the ACE Provider now contains the following new paragraph:
The Office System Drivers are only supported under certain scenarios, including: ...
2.To transfer data between supported file formats and a database repository, such as SQL Server. For example, to transfer data from an Excel workbook into SQL Server using the SQL Server Import and Export Wizard or SQL Server Integration Services (provided the SSIS jobs run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive).
SSAS Program Manager Dave Wickert explains why the last requirement is critical:
ACE uses the impersonated user’s Windows temp folder to read-write its data. Therefore if your application is using impersonation with an account that does not have a profile on the server (not an uncommon situation), then ACE will not be able to create its temp files.
This is good news for all those who are using SSIS with Excel. The Office team that owns the ACE Provider finally heard you roar!