Unspecified error in SSIS Script task after ACEOLEDB update

Question

Unspecified error in SSIS Script task after ACEOLEDB update

Jim_K

SSCrazy

Points: 2418
More actions
April 29, 2022 at 2:19 pm

#4024246

Has anyone experienced a script task or For Each ADO.NET Schema Rowset enumerator breaking after receiving an update to the ACEOLEDB via Microsoft 365 Access Runtime update? I was recently pushed an update to version 16.0.15028.20094 and that broke SSIS packages using the ACEOLEDB driver (Microsoft.ACE.OLEDB.14.0/Microsoft.ACE.OLEDB.16.0). They will all fail with an exception of "unspecified error"
Interesting enough I built a quick C# program to test the driver outside of SSIS and behaved as expected. It seems to be something between SSIS and the driver, but I'm not sure how to debug further. I tried using sysinternals Process Monitor to see if anything obvious showed up but I didn't see anything.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

Is this failing locally, in VS, on the server, or everywhere? Have you installed both 32- and 64-bit versions of the driver?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jim_K SSCrazy Points: 2418 More actions · Answer 2

I was able to reproduce it failing in VS on my dev workstation, but then was able to work around it. Originally I had a for each loop using the ADO Schema Rowset Enumerator to loop through the worksheets in the Excel file. I rewrote it as a script task that does the same thing and that allowed it to work when running within visual studio. I also wrote a quick and dirty C# program that did the same thing for testing independently (see below). That will work on the prod server (when compiled to force 64bit mode) even though there are some strange exceptions in event viewer that get logged -- but evidently don't bubble up to my program. However run the same code in context of the SSIS service and it just fails with the "Unspecified Error" exception. My best guess right now is this could be the source of the issue.

I've opened up a ticket with Microsoft to see if I can get this resolved or identified as a bug. Though it's now been over a week and still no time with a Microsoft tech to even begin working on the issue, which is quite frustrating.

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 = row["TABLE_NAME"].ToString();
                i++;
            }

            for (int k=0; k < excelSheets.Length; k++)
            {
                Console.WriteLine(excelSheets[k]);
            }

            Console.WriteLine("Complete.");
        }
    }
}