question

JoshK-9165 avatar image
0 Votes"
JoshK-9165 asked ·

Occasional errors connecting to Excel from SSMS and Visual Studio

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.


65959-sql-server-and-visual-studio-error-message.png


vs-generalsql-server-integration-services
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered ·

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


· 7 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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.

0 Votes 0 ·

@JoshK-9165,
Sounds good.

0 Votes 0 ·
JoshK-9165 avatar image JoshK-9165 YitzhakKhabinsky-0887 ·

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.

0 Votes 0 ·
Show more comments
Monalv-msft avatar image
0 Votes"
Monalv-msft answered ·

Hi @JoshK-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.



· 6 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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.

0 Votes 0 ·
Show more comments
JoshK-9165 avatar image
0 Votes"
JoshK-9165 answered ·

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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JoshK-9165 avatar image
0 Votes"
JoshK-9165 answered ·

Still needing help with this. Does it matter that Visual Studio is 2012 and SQL Server is 2016?

· 5 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JoshK-9165 ,

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

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

Best Regards,
Mona

0 Votes 0 ·

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?70148-ssis-project-properties.jpg70149-ssdt-version-note.jpg


0 Votes 0 ·

@JoshK-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.

1 Vote 1 ·
Show more comments
FarhanJamil-5363 avatar image
0 Votes"
FarhanJamil-5363 answered ·

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

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@FarhanJamil-5363,

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.

0 Votes 0 ·
JoshK-9165 avatar image
0 Votes"
JoshK-9165 answered ·

Unfortunately the problem has returned, even though I've upgraded to VS 2019, updated all my Excel Connection Managers to Excel 2016 and updated my database connections to SQL Server 2016. The crash always happens when opening an SSIS package, on the "Validating Excel Destination" step as the package opens.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@JoshK-9165,

Please try to set DelayValidation to True.

delay-validation-properties-in-ssis


0 Votes 0 ·