Search code examples
sql-serverms-accessssis

SSIS package with Microsoft Office 12 ACE OLEDB driver resulting to 'Unexpected Termination' status when executing from SSISDB catalog

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.

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?

Solution

  • 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