External component as thrown an exception

[Dellis81]

Hello - Since Wed Feb 5th, I have really been challenged with multitude of refresh errors.  Primarily related to External component has thrown an exception or unknown error.   As I go look at queries, seems to be related to a folder connection linking to mdb/MDB files.  I have created a very small test query - to these mdb files, and seems as I go step thru the query the first time or two, things are ok.   However if I attemt to do any transformation (select columns, filter, format to text - I get the error message below.   

One my bigger data model, this was working just as recently as 2 days ago - and now after trying on multiple computers, and older backup files - this error continues to haunt me..     1)  This was working within the last 2 days. 2)  Prior backups seem to work at first, then fail.  3)  Tried on multiple computers.  4)  I have isolated the two mdb queries to their own testing file,

I'm stumped, and need a solution!

Thanks for your thoughts!.

I have filtered out any rows with errors (there are none)   

[Rich_P]

I hope this isn't a double-post. My last reply errored-out, and I don't see it in this thread. So I'm trying again.

I have begun seeing this problem just this week. I am using a remote Win 10 VMWare machine via RDP at a client's site. The PBI Desktop file has worked fine for a few months. It connects to a single query in a very small Access.accdb file (492K).

This morning I updated to the Feb 2020 release and had the same problem.

I have been trying to convince the client that PBI would be better than the Excel solution I started them off with. They are interested and want me to build it out with some more features before showng the President. This problem puts all of us developers in a bad position. I have another client that is chomping at the bit to use this stuff internally AND for THEIR clients! Right now, I would have to tell them to stand by.

I hope MS will escalate this and get a solution fast.

[Dellis81]

Hi Rich! I'm with you on the urgency and need to keep everyone happy. Delays like this, only dampens enthusiasm for those we promise...

The technician led and encouraged me to use the OBCD driver. As you go thru the steps, you will be prompted for excel or access. For my simple test, that solution seemed to work.

Part of my challenge, I'm connecting thru a folder, with multiple access files. This OBDC solution is good for a single file/query. I've got 10-15 files, and not excited about a short term surgery, not knowing if a fix is quick to come or months...

The technician was to get back to me once they got past the investigation stage.

[Rich_P]

Thanks for the hint. Did the tech recommend ODBC over OLE DB for any reason? 

Am I to understand you are tapping 10-15 databases in a folder? If so, would it help you to build a new database file and link to the pertinent tables in each of the 10-15 files just so you can consolidate them into a single source as far as PBI is concerned?

[Anonymous]

I have a screen share session with an MS support rep, Power BI expert, and Sharepoint expert tomorrow morning.  I get this error when connecting to a Sharepoint folder and pulling tables out of Excel files in those folders and appending those tables together in Power BI.

The process has been working for a year and a half and since Feb. 10 or so, I haven't had one successful refresh in Power BI Desktop.  I've got the issue escalated so that they have 3 people connecting with me on one call tomorrow.  Hope to find a solution...

Will keep you guys posted.

[Rich_P]

Any luck with the Experts?

[Anonymous]

The "expert" I spoke to, had me install Fiddler and needs a trace of my refresh that fails.  Unfortunately, I can't use the internet during this time, and my report runs for over an hour, so I can't sit at work and do nothing while waiting for a refresh.  I'm trying to figure out a convenient time to get this Fiddler trace for them so they can diagnose the problem.

[Dellis81]

Hi Rich - no, the latest communication with support was late last week.  Feb 27/28th - or so.   The case is still under investation - with no known solution date.

I did attempt to go down the ODBC route on desktop PBI - and does seem to be working better.   However, with occassional hiccups.   I then ventured to attempt the import process using ODBC connector within the Service dataflow, and in my simple testing case-no refresh failures.

Thanks for your suggestion on linking files.   However, I am a "greenie" in this stuff -and my research didn't get me very far.  But, yes, linking files would be a solution.   As data is updated, what is the process to 'reload" the date into a linked mdb file.  Is it manual?

What I have are annual mdb files - with potentially 20+ years of data.   But, for now, I am limiting my import to 4 years (files).  But, the challenge - I need two tables within the mdb file, thus doubling the import queries.   With the older "folder" connector - the process was straight forward.   I have 3 differing sets of annual mdb files to deal with.  (3 sets*x2tables x 4yrs) = 24 potential queries.

Will keep everyone posted.  I was hoping the tech would get back to me after this weekend's email informing her of my ODBC efforts.

[Anonymous]

seems like I'm having the same issue.

doesn't work properly with access.

all of you guys are saying that the problem starts in February. Do you have a February version right?

try to do it with a previous one, not the latest?

maybe its something with the database engine connector.

[Dellis81]

Hello everyone!

I have been in communication w/Microsoft support - and they encouraged me to change from a folder connection to the pure Access (single file) connection.   After doing so - my test files seem to be working better, but still occcassional hiccups   After making the suggested changes to my larger data model -  100% failures on attempted refreshes.

All I can offer right now - is to encourage everyone to submit their own case.  Maybe get enough people with same errors - might create a sense of urgency.  https://powerbi.microsoft.com/en-us/support/free/    See lower right corner for submitting a case.   I will add, the technician I am working with does seem very diligent in her efforts - I think it's moving the problem up the ladder is the bottleneck.

I am using the Dec release of PowerBI.   There was no January release, not sure of Feb.   My problems started around Feb 4/5th - but was working before this date.

[Anonymous]

I'm also in contact with Microsoft support.  The technician was unable to pinpoint the problem when I had a Teams meeting with him on Friday.  He is supposed to bring along a Sharepoint expert and schedule another call this week to try to get to the bottom of this.

I'm also running the Power BI release from December 2019.  There hasn't been an update in January or February yet.

I will update once I have my follow up with the MS tech and Sharepoint expert.

[Dellis81]

Hello - I received an email from MS support - and this case (along with 2 other similar cases) have been escalated to the product development team.   Will be a month or so - upon hte next release.   We shalll see...

[AndyB87]

I had the same problem, and it turned out to be a simple permissions issue. Basically, my windows usr profile only had read and execute permissions for the Access file, which meant that I could load queries, but any transformations caused errors upon refresh.

The fix was: I navigated to the database file properties. Under the Security tab, I edited permissions and granted myself Full Control. Bingo! My queries are refreshing properly. 

I feel a little silly to have gotten hung up on such a basic thing, but then Power BI's error messages weren't the most helpful...

[Dellis81]

Andy - not sure that is my 'fix".   I checked out security permissions, and I have full permission to all attributes.

This is a fairly recent development and I am now finding same error message with a second pbix file with similar linkages to Access.   Again, this was all working till Feb 5th or so. 

I am still awaiting response from Micosoft support.

[Anonymous]

I'm having this exact same issue with my dataset.  For me, it started on February 10.  A routine refresh with new weekly files picked up from a Sharepoint folder is now causing an error saying "External component has thrown an exception."  

I get the option to send more details to Microsoft or cancel.  Either option leads to my refresh failing.  This has been quite frustrating as nothing has changed in my model.  This error is not helping us pinpoint the problem either.  It seems multiple people in this thread are having a similar issue.

[Anonymous]

I'm getting this error message repeatedly ("No error message available, result code: E_UNEXPECTED(0x8000FFFF)") - No explanation at all on how to correct this.

[dax]

Hi @Dellis81 ,

This might be related to the large data, you could refer to this post  for details. You said it also seems to be related to the data type, so you could try to import this data into other datasource , then you connect ti it to see whether it work or not.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dellis81]

Thank you Zho

The error I initially reported takes place when I step thru various queries.   I have since isolated the problem (I think) to connections to Access databases (mdb).

Basically - I created a whole new pbix file, with only the initial data ACCESS importing queries.   I am finding now, the refresh from the PowerBI ribbon works properly the first time, but second and consecutive attemps I get this error.

I also find it interesting, during the refresh process, the import status is provided.  First - those with TAD are showing as pulled from a local directory "C drive" and in rows.   Versus the CAD import, only shows the GB being pulled in.

But, as you can see below, file a lcoations are in same directory - relatevely small in size, with the CAD no where close to GB magnitude.

I greatly appreciate anyone's guidance -as this is a real hinderance to my progress.

thanks

[Anonymous]

I'm facing the same error since a week (in files and models that used to work perfect!). Ones I get are along similar lines with no error description available (often with code: E_UNEXPECTED(0x8000FFFF). 

Anyone with a solution yet?

[Dellis81]

I was in contact with Microsoft support yesterday (Monday Feb 10th), technician was very diligent and understood the problem.  I have not had any followup today.   As I know, I will respond back to this thread.

[Anonymous]

Awesome - Thank you so much!