Occasional errors connecting to Excel from SSMS and Visual Studio

Josh K 81

I don't know if this is a SQL Server issue, an Office issue, a Windows issue or something else. Windows 10, Office Pro 2016, SQL Server 2016 using Management Studio 18 as a client, and Visual Studio 2012. Last year we modified our database access to use Windows authentication, using a Windows Service account. Along with that change I was instructed to open my SQL client and my Visual Studio SSIS tools by using “Run as a different user” so I can open them with that service account. Over the last few months I’ve had an intermittent issue (once or twice a week) where attempting to perform any task in the SQL client or Visual Studio that involves importing from or exporting to MS Excel results in the attached error, which can only be cleared by using Task Manager to force quit the application. In Visual Studio the error occurs when attempting to open any package that has an Excel connection manager in it, and it hangs on that validation step. The only way to resolve it is to perform a quick repair on MS Office. I have confirmed that this only happens if I use “run as a different user” to open my SQL client or Visual Studio. I’ve confirmed that the service account has the needed permissions on the program folders for MS Office, SQL Server Management Studio 18, and Visual Studio 2012.

Accepted answer

Yitzhak Khabinsky 24,936 Reputation points

2021-03-02T20:56:38.74+00:00

The crash always happens when opening an SSIS package, on the "Validating Excel Destination" step as the package opens.

So, it is a design-time error.

(1) Please try to set DelayValidation to True.
delay-validation-properties-in-ssis

(2) Additionally, there is a global setting in VS2019 to do the same:
skip-validation-of-ssis-packages
Please sign in to rate this answer.
Josh K 81 Reputation points

2021-03-02T21:16:05.54+00:00

Thanks - I've set the property to True for one package and also for all of the Excel connections in it. I also changed the global setting. Since this is a sporadic issue I may not know whether this worked for several days. I will post back as soon as I have more to tell.

Josh K 81 Reputation points

2021-03-15T20:08:23.087+00:00

So far I think that was the fix, but I'm not positive yet. The issue hasn't recurred since I made this change. I've also been opening backup copies of my packages in VS 2012, without this change in place, to see if the issue still happens there. If it does then I know for sure that was it, but so far it hasn't. This is the longest I've gone without seeing the error. I want to wait a few more days before considering this issue closed.

Yitzhak Khabinsky 24,936 Reputation points

2021-03-15T20:19:24.343+00:00

anonymous user-9165,
Sounds good.

Josh K 81 Reputation points

2021-03-16T20:53:43.743+00:00

The issue still has not recurred. Since I deliberately didn't change the Delay Validation property in Visual Studio 2012 in order to test this, the only other thing I can think of is that I had the updated Microsoft ACE OLEDB provider installed on my database servers. I wouldn't think a package would be checking for that when validating an Excel file connection, though, and the Excel files are stored on a different server. If the issue recurs I'll post back but for now I'll mark your last suggestion as the answer. Thanks very much for your help.

Josh K 81 Reputation points

2021-03-26T20:18:35.493+00:00

Changing the Delay Validation property successfully prevented the error from recurring in Visual Studio. The other day I got the same error trying to import data from Excel using the Import and Export Wizard in SQL Server Management Studio (v18), so I followed Mona's suggestion below and installed the 32-bit version of the MS Access Database Engine on my laptop, then tried the wizard again and it worked. So it looks like changing the delay validation property is a good workaround but the Access Database Engine version may be the actual source of the problem. Strange that Excel 2016 and SQL Server 2016 would have this kind of issue communicating with each other but I think I'm good now. Thanks to everyone for their help.

Yitzhak Khabinsky 24,936 Reputation points

2021-03-26T20:59:22.55+00:00

anonymous user-9165,
Good to hear good news from your side.

Josh K 81 Reputation points

2021-04-14T16:11:35.42+00:00

Looks like I still have an issue. In addition to setting the Delay Validation properties I've also installed the ACE provider on my servers and on my laptop (see those suggestions below). The error no longer appears but now I occasionally have either Visual Studio or SSMS simply crash when trying to import/export from Excel. And again, doing an Office repair fixes it temporarily.

It's more of an annoyance than a problem at this point but I'd sure like to get it figured out.
Sign in to comment

5 additional answers

Monalv-MSFT 5,891 Reputation points

2021-02-10T03:26:38.91+00:00

Hi anonymous user-9165 ,

May I know if you download and install Microsoft Access Database Engine?

Please refer to the following links:
1.Microsoft Access Database Engine 2016 Redistributable
2.Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Josh K 81 Reputation points

2021-02-11T16:49:43.29+00:00

I can now confirm that after installing the 64-bit provider and rebooting, the issue is not resolved.

Monalv-MSFT 5,891 Reputation points

2021-02-12T09:50:10.647+00:00

Hi anonymous user-9165 ,

Please refer to the following links:

1.Excel Connection Manager

2.Import data from Excel or export data to Excel with SQL Server Integration Services (SSIS)

Best Regards,
Mona

Josh K 81 Reputation points

2021-02-12T14:47:19.687+00:00

Is there a specific property of the Excel Connection Manager I should be checking? I'm already using the Excel Connection Manager (which is where the trouble happens), set to use Excel 2007 as the file format. There are no newer versions available.

Monalv-MSFT 5,891 Reputation points

2021-02-24T06:47:33.603+00:00

Hi anonymous user-9165 ,

May I know if you have anything to update?

Best Regards,
Mona

Josh K 81 Reputation points

2021-02-24T14:39:13.303+00:00

Hi, Mona:

Yes, I installed VS 2019 on Friday, updated my project properties and also updated all of the Excel Connection Managers to Excel 2016, and so far all looks good. I was waiting because the issue didn't happen every single time and I wanted to monitor it over several days. But at this point I think the issue is resolved. It was also happening when trying to import from Excel in SSMS 18, but I'm guessing the VS 2019 install includes an OLE DB provider that's more compatible with Excel 2016. I'll post back if the error returns but for now I think we can consider this resolved. Thanks very much for all your help!

Yitzhak Khabinsky 24,936 Reputation points

2021-02-24T14:52:20.027+00:00

anonymous user-9165,

I'm guessing the VS 2019 install includes an OLE DB provider that's more compatible with Excel 2016.

No, VS2019 doesn't install any ACE Providers.
Sign in to comment
Josh K 81 Reputation points

2021-02-11T16:26:38.75+00:00

Hi, Mona:

Thanks, I just followed the instructions to install the 64-bit version. My OLE DB Provider is version 14, not 12. Does that matter?

Unfortunately, this did not resolve my issue (even after a reboot).

Thanks,

Josh
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Josh K 81 Reputation points

2021-02-17T21:40:07.543+00:00

Still needing help with this. Does it matter that Visual Studio is 2012 and SQL Server is 2016?
Please sign in to rate this answer.
Monalv-MSFT 5,891 Reputation points

2021-02-19T09:50:51.133+00:00

Hi anonymous user-9165 ,

It's better for you to use newer version Microsoft Access Database Engine.

If your Visual Studio is 2012 and SQL Server is 2016, we should set the TargetServerVersion as SQL Server2016.

Best Regards,
Mona

Josh K 81 Reputation points

2021-02-19T17:33:17.317+00:00

I'm not seeing where I can set the target server version. Attached is a screenshot of my project properties window. I checked my version of SSDT and there was a slightly newer version available for download, which I did, but that didn't change anything. The download page included the attached disclaimer; maybe that's why?

Yitzhak Khabinsky 24,936 Reputation points

2021-02-19T17:37:28.37+00:00

anonymous user-9165,

Your Visual Studio 2012 is too old. That's why you don't have that setting.

It is better to use VS2019.
it supports SSIS development for SSIS 2012, 2014, 2017, and 2019.

Josh K 81 Reputation points

2021-02-19T17:41:04.923+00:00

Thanks, I thought that might be it. I have VS 2019 and will install and see if that resolves the issue.

Josh K 81 Reputation points

2021-02-19T21:38:37.087+00:00

I have installed VS 2019 and the SSIS extension, changed the SQL Server connection to SQL Server 2016 and converted all of my Excel connection managers to Excel 2016. I will monitor next week and post back.
Sign in to comment
Farhan Jamil 416 Reputation points

2021-02-19T23:05:27.257+00:00

Hello

Please make sure that the deployment of excel is in 32 bit version as excel doesn't support 64 bit version deployment

May be this may help .

Farhan Jamil
Please sign in to rate this answer.
Yitzhak Khabinsky 24,936 Reputation points

2021-02-21T01:54:59.387+00:00

@Farhan Jamil ,

There is no need in MS Excel at all.
It is about the OLEDB provider to access Excel files on the server.
Microsoft ACE OLEDB provider has 64-bit edition for a reason.
Sign in to comment