I had issues getting SSIS to connect to Excel files so I installed the 64bit Access Database Drivers, but it still didn't work. I used the /passive option to also install the 32bit Access Database Drivers along side the 64bit. Only then would it connect to the Excel files!
I am now trying to use the SQL Server Import and Export Wizard to import an Excel file (different file) and I am getting the following error. I am getting the error all 3 of the files I am trying to import. I also tried a file that I have previously imported many times and also received the error.
This has my work at a standstill right now, so help is greatly appreciated!
External component has thrown an exception. (System.Data)
------------------------------
Program Location:
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.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 Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetOpenedConnection(WizardInputs wizardInputs, String connEntryName)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnLeavePage(LeavePageEventArgs e)
Edit: I spent a good 6 hours trying to get everything installed and it was the ONLY combination that would allow me to connect to Excel files in my SSIS package... In the end this is what I did.
- Wiped Drive and performed completely clean Win 11 Pro install
- Installed Office 365, Rebooted.
- Installed 64bit Access Database Drivers, Rebooted
- Installed 32bit Access Database Drivers using /passive switch, Rebooted
- Installed SQL Server 2019 Dev, Rebooted
- Installed Visual Studio 2019 Community, Rebooted
- Installed Integration Services, Sacrificed a bag of Chips Deluxe, Rebooted
- Rebuilt SSIS Package from Scratch, Confirmed it Worked.
- Attempted to Import a different Excel file using the 64bit Import Wizard located in the Start Menu, error.
I did figure out that if I load the Import Wizard from SSMS that it will open the wizard an it will work from there.