admin管理员组

文章数量:1125345

We are trying to run SSIS package to read Access Database (.mdb) files using Native OLE DB\Microsoft Office 12 Access Database Engine OLE DB Provider in the Source Connection Manager. This package ran successfully from Visual Studio 2019 and also from SSISDB catalog in lower end servers.

However, executing the package in higher environments resulted in 'Unexpected Termination' status in SSISDB catalog without any error message. Tried changing different properties available in Source Connection Manager but all resulted in the same 'Unexpected Termination'.

(.png)

Debugging it with help of DBA, we found out that the package executes successfully if the executor/caller of the SSIS package has admin privileges(like the DBA) in the server. As it turns out the successful execution in lower environments was also because of the admin privileges the executor had in the server. Revoking the admin privilege in lower environment(UAT) also seemed to show the same status.

Is the admin privilege an absolute necessity to run packages with ACE OLE DB providers? Or are there any missing areas we need to check?

Any answers or ideas would be very helpful. Thanks.

Tried an alternative driver - 'Microsoft Jet 4 OLE DB' and the execution from SSISDB catalog was successful. Are there any advantages or disadvantages of using 'Microsoft Jet 4 OLE DB' driver?

We are trying to run SSIS package to read Access Database (.mdb) files using Native OLE DB\Microsoft Office 12 Access Database Engine OLE DB Provider in the Source Connection Manager. This package ran successfully from Visual Studio 2019 and also from SSISDB catalog in lower end servers.

However, executing the package in higher environments resulted in 'Unexpected Termination' status in SSISDB catalog without any error message. Tried changing different properties available in Source Connection Manager but all resulted in the same 'Unexpected Termination'.

(https://i.sstatic.net/oJNksTuA.png)

Debugging it with help of DBA, we found out that the package executes successfully if the executor/caller of the SSIS package has admin privileges(like the DBA) in the server. As it turns out the successful execution in lower environments was also because of the admin privileges the executor had in the server. Revoking the admin privilege in lower environment(UAT) also seemed to show the same status.

Is the admin privilege an absolute necessity to run packages with ACE OLE DB providers? Or are there any missing areas we need to check?

Any answers or ideas would be very helpful. Thanks.

Tried an alternative driver - 'Microsoft Jet 4 OLE DB' and the execution from SSISDB catalog was successful. Are there any advantages or disadvantages of using 'Microsoft Jet 4 OLE DB' driver?

Share asked 2 days ago GaganGagan 1 New contributor Gagan is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 4
  • This question is similar to: SSIS package fails when runs by sql job agent. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. – Filburt Commented 2 days ago
  • Well, a point against the ACE 12 OLE DB drivers is that Microsoft stopped supporting them and removed all downloads for them back in 2020. – AlwaysLearning Commented 2 days ago
  • Yep, an infinitely stupid decision, @AlwaysLearning , when you can't have both the 32bit and 64bit driver installed at the same time, but Visual Studio was still a 32bit application and so (too) was SSIS development; making it impossible for people to develop SSIS packages against Excel if they had 64bit Office installed. – Thom A Commented 2 days ago
  • Main problem is that microsoft put too much dependencies on their drivers against registry and other non-portable goo (probably for commercial reasons but still) making them all but very unusable for a lot of people. I'm also guessing the ACE drivers likely used some sort of .COM voodoo with message pumping which usually needs user interaction and hidden windows instead of just being a normal dll – siggemannen Commented 2 days ago
Add a comment  | 

1 Answer 1

Reset to default 1

What you're doing is unsupported, so it's expected that it only works partially, or with certain users. The issue may not be about permissions per se but running as a user who has (or is) logged on interactively.

The Office drivers are only intended to work in desktop applications:

The Office System Drivers are only supported under certain scenarios, including:

Desktop applications which read from and write to various files formats including Microsoft Office Access, Microsoft Office Excel and text files. To transfer data between supported file formats and a database repository, such as SQL Server. For example, to transfer data from an Excel workbook into SQL Server using the SQL Server Import and Export Wizard or SQL Server Integration Services (provided the SSIS jobs run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive).

https://www.microsoft.com/en-us/download/details.aspx?id=54920

本文标签: