Long story short, I've had to use a workaround to importing data directly from SharePoint due to SSIS drivers not working with it. My current workaround is almost good enough, but it still has some issues.
I started out with a regularly running report from SSRS that dumps into Excel 2003 files. Turns out sometimes data completely gets left off of certain columns when using the SQL Import Wizard, so I switched to .xlsx files. Now the data, when the import actually works, shows up, but there's a unique quirk with this in the .xlsx files where the import wizard throws the "External table not in expected format error" UNLESS the .xlsx files are manually opened, then immediately saved and closed. After that, the import works without issue.
What could be causing this particular quirk, and is there any way to systematically get around it? If this were a one-time deal, I wouldn't be taking issue, but this data will have to be regularly refreshed.
If it helps... .
- the server this is on is a 2012 R2 Datacenter
- running SQL 2014
- no Microsoft office programs are installed on this server, just drivers
- SSRS is version 2012 (on a different server)
There are two installations for Microsoft Office Access database engine; 2007 and 2010, which I needed to install to get the Excel --> SQL push to even work in the first place.