Link to home
Start Free TrialLog in
Avatar of Karl_mark
Karl_mark

asked on

SSIS OLE DB Excel Connection Error: External Table not in expected format

I have created a data migration project which takes '|' delimited flat files, imports them to SQL Server, processes the data, and then outputs to an Excel Template for loading into a new system. I have no responisbility for either the flat files, or the excel template as they are handled by third parties.
I am running on Visual Studio 15.9.5 for the SSIS development, and the package will be used only by myself and will not be distributed to anybody else. Connection is to SQL Server Azure.

I have had no issues with connecting importing the flat files (beyond the usual bad data problems) or connecting to SQL Server and running scripts. The issue I'm having is with the final stage in the process which involves transferring the contents of SQL tables to Worksheets within an Excel Workbook. I am running Excel on Office 365 and all processing of files is done on my laptop.

When I ran an initial dump to Excel I had some errors with the version, so I changed it to XLS to resolve any compatibility issues (which I have done before successfully). However, on this particular project, the limit for XLS files was breached as there are almost 70,000 rows of data to export. When I changed to XLSX I got another error, so I installed Microsoft Access Data Engine 2010 which seemed to work on some test runs.

Now I have finished all of the mapping, so a little more about the spreadsheet. There are multiple worksheets to which I have to export data; I have created Data Flow tasks which use an ADO.net connection (the only one I can use with Azure) to read from a table, and then a Microsoft Excel Destination which points to the spreadsheet held locally. All mappings have been set up and there are no build errors.

When I run the package, I get through all of the import and transform steps with no problems. However, when I hit the output steps I keep getting an error. The error does not appear consistently at the same step; some of the output steps will succeed, but eventually one will fail:

Error: 0xC0202009 at MainImport, Connection manager "MasterWorkDayTemplate": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft Access Database Engine"  Hresult: 0x80004005  Description: "External table is not in the expected format.".
Error: 0xC020801C at Output Employee Contracts, MasterWorkDayTemplate [2]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "MasterWorkDayTemplate" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC004701A at Output Employee Contracts, SSIS.Pipeline: MasterWorkDayTemplate failed the pre-execute phase and returned error code 0xC020801C.

In the error above it fails on the Output Employee Contracts step, but in previous runs it has failed on steps before or after the one above. I have tried refreshing the Excel Connection using Advanced Editor to no avail. I have checked the Excel Connection Manager and there do not seem to be any issues (bear in mind that the same connection manager is used in steps that do succeed). I'm now at a bit of a loss! Perhaps I need to update to a newer version of the Access Database engine? Or is there another setting such as the 64 bit toggle in properties that would help?
Avatar of lcohan
lcohan
Flag of Canada image

Does the excel output has multiple sheets or does it require any formating/calculated cells?  If not, I would export them as CSV instead as CSV file's can be opened in any version of Excel and many other different softwares
Avatar of Karl_mark
Karl_mark

ASKER

The excel output does have multiple sheets, but does not requre any formatting and has no calculated cells; it is a straight export of data from SQL tables to Excel worksheets.

I've managed to get a bit further; I've reinstalled the Microsoft Access 2010 Database engine and no longer get the error. The new problem is that the package simply hangs on one step. I am actually able to see that it opens the spreadheet to the correct worksheet, but nothing happens. The step in the flow shows it as processing, but I've left it for an hour and a half and nothing happens. If I close Excel then I get an error saying the field cannot be updated.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.