View Single Post
Old 06-16-2014, 08:17 PM
Abumushfiq Abumushfiq is offline Windows 7 64bit Office 2010 32bit
Join Date: Jun 2014
Posts: 2
Abumushfiq is on a distinguished road
Default Strange Excel Behaviour with ACE OLEDB Provider

Hi Everyone,

In our MSBI assignment, we are having separate environment for TEST and Production respectively.

We are fetching the Excel file from SharePoint and loading into SQL Server
using SSIS. So as said, each of these module will have similar setup in both environments.

Our issue is with an Excel file from PROD sharepoint is not loaded properly thorugh ACE OLEDB 12 proivider. It is loading null for few cells. which was working so far earlier.

Following are our attempts to fix the issue:

1. We took the file from PROD and drop it in TEST sharepoint, there it is getting loaded fine.
2. We took the SSIS package from Production code and deployed the same into TEST, there it is working fine picking the files from TEST sharepoint.
3. With several other attempts, we confirmed that there is no issue with Excel source file, SSIS package etc.
4. Then on researching further, we found that there is a difference in system registry of the servers. In TEST server, ACEEXCL dll from Office 14 is referred as Excel Engine. whereas in Production ACEEXCL dll from Office 12 is referred as Excel Engine. Attaching snapshots for your reference.
5. We also tried modifying TypeGuessRows (as you see in the snapshot there is difference in this settings too), but it doesn't work out.

Please confirm the above difference and let me know the steps to fix this if it is the cause. If not kindly key down your valuable suggestions to proceed further.


Attached Images
File Type: jpg Production_Snp.jpg (134.0 KB, 15 views)
File Type: jpg TEST_Snp.jpg (164.8 KB, 15 views)
Reply With Quote