question

JimKing-8584 avatar image
0 Votes"
JimKing-8584 asked JimKing-8584 published

SSIS Script Task failing after ACE OLEDB driver update -- Unspecified Error

It seems we've received an update for the ACE OLE DB driver signed on 2022-03-16 and that it has broken our SSIS 2019 instance

We use the driver to enumerate worksheets in an Excel file while staging data and now it simply fails with an exception "Unspecified Error" in System.Data.OleDb.OleDbServicesWrapper.GetDataSource()

We've uninstalled and reinstalled the 64 bit ACE OLE DB runtime and I used a simple C# program to test that indeed it works (outside of SSIS) with two different connection strings:

  • Data Source= test.xlsx;Provider=Microsoft.ACE.OLEDB.16.0;Extended Properties = Excel 12.0;

  • Data Source= test.xlsx;Provider=Microsoft.ACE.OLEDB.14.0;Extended Properties = Excel 12.0;

In each case it does we expect it to, however when running similar code within the SSIS package it fails with the aforementioned "Unspecified Error" exception when connection.Open() is called.

One other strange thing we do see when running our test program is that there is an "Application Error" logged in event viewer -- but it runs fine.

Faulting application name: TestOLEDB.exe, version: 1.0.0.0, time stamp: 0xf06749a9
Faulting module name: mso20win32client.dll, version: 0.0.0.0, time stamp: 0x62594778
Exception code: 0xc0000005
Fault offset: 0x000000000004b9d1
Faulting process id: 0x2d90
Faulting application start time: 0x01d85b4cf5f9f33d
Faulting application path: K:\TempData\TestACE12\TestOLEDB.exe
Faulting module path: C:\Program Files\Common Files\Microsoft Shared\Office16\mso20win32client.dll
Report Id: adccaec3-1a08-4683-a453-874cae04b9a6
Faulting package full name:
Faulting package-relative application ID:

Sample test code:

 using System;
 using System.Data;
 using System.Data.OleDb;
    
 namespace TestOLEDB
 {
     class Program
     {
         static void Main(string[] args)
         {
             string connectionString = @"Data Source= test.xlsx;Provider=Microsoft.ACE.OLEDB.16.0;Extended Properties = Excel 12.0;";
    
             OleDbConnection connection = new OleDbConnection(connectionString);
    
             Console.WriteLine(connection.ConnectionString);
    
             DataTable dt;
    
             connection.Open();
    
             dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    
             string[] excelSheets = new string[dt.Rows.Count];
    
             int i = 0;
    
             foreach(DataRow row in dt.Rows)
             {
                 excelSheets[i] = row["TABLE_NAME"].ToString();
                 i++;
             }
    
             for (int k=0; k < excelSheets.Length; k++)
             {
                 Console.WriteLine(excelSheets[k]);
             }
    
             Console.WriteLine("Complete.");
         }
     }
 }

Exception stack trace from SSIS task:

Unspecified error
at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at ST_f86e1e071b75404b9c63b786feba1637.ScriptMain.Main()



sql-server-integration-services
· 1
5 |1600 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.

ACEOLEDB.dll info:

 PS C:\Windows\system32> (Get-Item "C:\Program Files\Microsoft Office\root\vfs\ProgramFilesCommonX64\Microsoft Shared\OFFICE16\ACEOLEDB.DLL").VersionInfo | fl *
    
    
 FileVersionRaw     : 16.0.15028.20094
 ProductVersionRaw  : 16.0.15028.20094
 Comments           :
 CompanyName        : Microsoft Corporation
 FileBuildPart      : 15028
 FileDescription    : Microsoft Access database engine OLE DB Provider
 FileMajorPart      : 16
 FileMinorPart      : 0
 FileName           : C:\Program Files\Microsoft Office\root\vfs\ProgramFilesCommonX64\Microsoft
                      Shared\OFFICE16\ACEOLEDB.DLL
 FilePrivatePart    : 20094
 FileVersion        : 16.0.15028.20094
 InternalName       : aceoledb
 IsDebug            : False
 IsPatched          : False
 IsPrivateBuild     : False
 IsPreRelease       : False
 IsSpecialBuild     : False
 Language           : English (United States)
 LegalCopyright     :
 LegalTrademarks    :
 OriginalFilename   : aceoledb.dll
 PrivateBuild       :
 ProductBuildPart   : 15028
 ProductMajorPart   : 16
 ProductMinorPart   : 0
 ProductName        : Microsoft Office
 ProductPrivatePart : 20094
 ProductVersion     : 16.0.15028.20094
 SpecialBuild       :
0 Votes 0 ·

0 Answers