Debug SSIS import from Excel to SQL Server

• Mark Dalley

  SSCrazy

  Points: 2996

  More actions

  July 22, 2020 at 6:39 pm

  #3773928

  My apologies, Jeffrey. I must have been skimming and missed the question at the end of your post. I just saw it now when I was checking the thread.

  The answer is yes, the package did start working on both workstations. I had hoped of course that that was the end of it -- which it was, until today! Arragh!

  It is still working on my machine, which has a 32-bit version of Office installed. On my colleague's machine, which has a 64-bit version of Office 2016, it now errors instantly when you try to move on after selecting the file and the Excel 2007-2010 source format in the wizard.

  The error message, which follows SSIS's tradition of wordy unhelpfulness, begins as follows:

  External component has thrown an exception. (System.Data)

  at System.Data.OleDb.DataSourceWrapper.InitializeAndCreateSession(OleDbConnectionString constr, SessionWrapper& sessionWrapper)

  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.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

  at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)

  ........................

  I strongly suspect that the problem was precipitated by a recent system update pushed out by our IT people.

  I did try getting my colleague to re-run "AccessDatabaseEngine.exe /quiet" from the command prompt, but it made no difference.

  If anyone has any other suggestions, I am all sensory receptors.

  MarkD

  P.S. I suppose I could start another thread, but I am reluctant to do so because of the amount of background explanation that would need repeating.

• Mark Dalley

  SSCrazy

  Points: 2996

  More actions

  July 24, 2020 at 5:38 pm

  #3774529

  Sorted it.

  Uninstalled both the 32-bit and the 64-bit AccessDatabaseEngine.exe 2016 drivers.

  Uninstalled all earlier versions of said drivers.

  Reinstalled the 32-bit AccessDatabase Engine.exe 2016 program from the command line using /quiet in order to avoid problems with the 64-bit Office installation.

  Impoprt from Excel now works OK. Long may it continue.

  MarkD