Unexpected Termination Excel Export - Microsoft.ACE.OLEDB.16.0

Question

Hi,

64 bit Microsoft.ACE.OLEDB.16.0 is installed on the server and the ssis package that exports data to excel fails with unexpected termination or doesn't export full data to excel tabs.

No error logs shown.

Please let me know if both 32 bit and 64 bit needs to be installed or any other installation is missing. The package works fine on local machines. Thank You

Answer 1

Hi @Challagundla, Sindhura (ITS),

You may have a try to install both 32bit and 64bit driver.

See:

https://datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/

Microsoft Access Database Engine 2016 Redistributable download.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

The problem where an SSIS package using Microsoft.ACE.OLEDB.16.0 fails to export data to Excel typically arises from a bitness mismatch between the installed drivers and the execution environment of SSIS. In simple terms, the SSIS package might be running in a different bit environment (32-bit or 64-bit) compared to the installed ACE.OLEDB driver.

When SSIS packages are developed or tested locally, they might run using a different bit version than when they are executed on a server. If, for instance, the SSIS package is executed in a 32-bit runtime environment but the server only has the 64-bit ACE.OLEDB driver installed, the package will fail to find the appropriate driver, leading to unexpected terminations or incomplete data exports.

Moreover, there may be subtle differences in the environment between local machines and servers. Local environments may have both 32-bit and 64-bit drivers installed, which allows the package to find the correct driver regardless of the bitness of the runtime. On the server, however, if only one version of the driver is installed, and it does not match the runtime environment of the SSIS package, the package will encounter errors.

Ensuring compatibility involves checking and possibly installing both versions of the ACE.OLEDB driver (32-bit and 64-bit) on the server. Additionally, configuring the SSIS package to explicitly use the correct driver version based on its execution environment will help in resolving these issues. This process might also involve adjusting the settings in the SQL Server Agent job or the SSDT project properties to match the driver version with the runtime environment.

Understanding and managing these bitness mismatches and ensuring that the correct drivers are available and used appropriately during SSIS package execution is key to resolving the issue.

Detailed Step-by-Step Solution for Unexpected Termination in Excel Export with SSIS using Microsoft.ACE.OLEDB.16.0

Addressing the unexpected termination or incomplete data export issue in SSIS (SQL Server Integration Services) packages when using Microsoft.ACE.OLEDB.16.0 involves a thorough examination of several factors. Here’s a detailed guide to resolve this issue:

Step 1: Verify ACE.OLEDB Driver Installation

Ensure that the correct version of the ACE.OLEDB driver is installed on the server. Since you mentioned that the 64-bit version is installed, verify this installation:

1. Check Installed Drivers:
   • Open the Command Prompt and run the following command to list all OLEDB providers:

     
          reg query "HKEY_CLASSES_ROOT\WOW6432Node\CLSID" /s /f "Microsoft.ACE.OLEDB.16.0"
     
     

   • This command should return the installation path if the driver is correctly installed.

Step 2: Install Both 32-bit and 64-bit ACE.OLEDB Drivers

In some scenarios, both 32-bit and 64-bit versions of the ACE.OLEDB driver may need to be installed, especially if the SSIS packages are developed or executed in different bit environments.

1. Download and Install Drivers:
   • Download the 64-bit ACE.OLEDB.16.0 driver and install it.
   • Download the 32-bit ACE.OLEDB.16.0 driver and install it.

Step 3: Configure SSIS Package Execution

Ensure that the SSIS package is configured to use the correct bit version during execution. The SQL Server Data Tools (SSDT) may use a different bit version during development than SQL Server Integration Services (SSIS) during execution.

1. SSDT Configuration:

Detailed Step-by-Step Solution for Unexpected Termination in Excel Export with SSIS using Microsoft.ACE.OLEDB.16.0

As a senior SQL Server Database Administrator, resolving the unexpected termination or incomplete data export issue in SSIS (SQL Server Integration Services) packages when using Microsoft.ACE.OLEDB.16.0 involves a thorough examination and step-by-step troubleshooting process. Here’s a detailed guide to resolve this issue:

Step 1: Verify ACE.OLEDB Driver Installation

Ensure that the correct version of the ACE.OLEDB driver is installed on the server. Since you mentioned that the 64-bit version is installed, verify this installation:

1. Check Installed Drivers:
   • Open the Command Prompt and run the following command to list all OLEDB providers:

     
          reg query "HKEY_CLASSES_ROOT\WOW6432Node\CLSID" /s /f "Microsoft.ACE.OLEDB.16.0"
     
     

   • This command should return the installation path if the driver is correctly installed.

Step 2: Install Both 32-bit and 64-bit ACE.OLEDB Drivers

In some scenarios, both 32-bit and 64-bit versions of the ACE.OLEDB driver may need to be installed, especially if the SSIS packages are developed or executed in different bit environments.

1. Download and Install Drivers:
   • Download the 64-bit ACE.OLEDB.16.0 driver and install it.
   • Download the 32-bit ACE.OLEDB.16.0 driver and install it.

Step 3: Configure SSIS Package Execution

Ensure that the SSIS package is configured to use the correct bit version during execution. The SQL Server Data Tools (SSDT) may use a different bit version during development than SQL Server Integration Services (SSIS) during execution.

1. SSDT Configuration:
   • In SSDT, navigate to Project Properties -> Configuration Properties -> Debugging.
   • Set Run64BitRuntime to False if you are using the 32-bit version during development and testing.
2. SSIS Execution Configuration:
   • If you are running the SSIS package from SQL Server Agent, ensure that the job step is configured correctly.
   • In the SQL Server Agent job, go to the Steps tab, select the SSIS package step, and click Edit.
   • In the Execution Options tab, check the box Use 32-bit runtime if you have installed the 32-bit driver.

Step 4: Debugging and Logging

To diagnose the issue further, enable detailed logging and error handling in your SSIS package.

1. Enable Logging:
   • In SSDT, right-click on the Control Flow design surface and select Logging.
   • Add a log provider (e.g., SSIS Log Provider for Text Files).
   • Select the events you want to log, such as OnError, OnWarning, OnInformation, etc.
2. Error Handling:
   • Add event handlers for OnError and OnTaskFailed events to capture detailed error information.
   • Use Try...Catch constructs within SSIS to handle potential failures gracefully and log the error messages.

Step 5: Test in a Local Environment

Since you mentioned that the package works fine on local machines, compare the environment settings between the local machine and the server:

1. Environment Comparison:
   • Compare the versions of installed drivers, SSIS, and SQL Server.
   • Check the bit versions (32-bit vs. 64-bit) used in both environments.
2. Replicate the Issue Locally:
   • Try to replicate the server environment on your local machine to identify any discrepancies.

Step 6: Additional Considerations

1. Data Volume:
   • Ensure that the data volume being exported is manageable within Excel’s limits. Excel has a limit of 1,048,576 rows per worksheet.
   • If exporting large datasets, consider splitting the data across multiple sheets or using another format.
2. Resource Availability:
   • Check the server’s resource availability (CPU, memory) during the export process. Resource constraints might cause unexpected terminations.
3. Excel File Format:
   • Ensure that the Excel file format (e.g., .xlsx) is supported by the installed ACE.OLEDB driver.
   • Consider using a different destination format to see if the issue persists.

By following these detailed steps, you should be able to identify and resolve the issue causing the unexpected termination or incomplete data export in your SSIS package using Microsoft.ACE.OLEDB.16.0. If problems persist, further analysis and perhaps a more tailored approach might be necessary.

Answer 3

The problem where an SSIS package using Microsoft.ACE.OLEDB.16.0 fails to export data to Excel typically arises from a bitness mismatch between the installed drivers and the execution environment of SSIS. In simple terms, the SSIS package might be running in a different bit environment (32-bit or 64-bit) compared to the installed ACE.OLEDB driver.

When SSIS packages are developed or tested locally, they might run using a different bit version than when they are executed on a server. If, for instance, the SSIS package is executed in a 32-bit runtime environment but the server only has the 64-bit ACE.OLEDB driver installed, the package will fail to find the appropriate driver, leading to unexpected terminations or incomplete data exports.

Moreover, there may be subtle differences in the environment between local machines and servers. Local environments may have both 32-bit and 64-bit drivers installed, which allows the package to find the correct driver regardless of the bitness of the runtime. On the server, however, if only one version of the driver is installed, and it does not match the runtime environment of the SSIS package, the package will encounter errors.

Ensuring compatibility involves checking and possibly installing both versions of the ACE.OLEDB driver (32-bit and 64-bit) on the server. Additionally, configuring the SSIS package to explicitly use the correct driver version based on its execution environment will help in resolving these issues. This process might also involve adjusting the settings in the SQL Server Agent job or the SSDT project properties to match the driver version with the runtime environment.

Understanding and managing these bitness mismatches and ensuring that the correct drivers are available and used appropriately during SSIS package execution is key to resolving the issue.