SSIS requirement at deployment server with Excel files as data source?

Question

Hello,

I have a SSIS that was created from Visual Studio 2019.

It loads the data from an Excel file to a table in a SQL database.

The development computer has Windows 10, M365, and Microsoft Access Database Engine 2016 (both 32 and 64 bits).

It runs successfully in the development computer.

However, after I deployed it to the SQL Server, the Integration Service Catalogs, it does not work.

The SQL Server resides on a Windows server, which does not have Excel or any Office applications. It has Microsoft Access Database Engine 2016 (both 32 and 64 bits).

I tried Run64bitRunTime (True/False) in Visual Studio project settings, and a combination of one (either 32 or 64 bit) or both 32 and 64 bits of Microsoft Access Database Engine 2016 at the server. Tried different order of installation of Microsoft Access Database Engine too. But none worked*.

Someone mentioned in the SQL agent job, there is an option to run a job in 32-bit, but we do not have that option.

Anyone has an idea on how to resolve it?

Thanks,

Andy

PS:

*

The errors were among these 3 below for my testing of

a combination of

Run64bitRunTime (True/False) and

one or both Microsoft Access Database Engine 2016 (32-bit and 64-bit):

1.

unexpected termination

2.

Package:Error: The requested OLE DB provider Microsoft.ACE.OLEDB.16.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

3.

External component has thrown an exception.; at Microsoft.SqlServer.Dts.Runtime.DtsContainer.Execute (Connections connections, Variables variables, IDTSEvents100 events, IDTSLogging100 log, Object transaction) at Microsoft.SqlServer.IntegrationServices.Server.ISServerEx ec.ISServerExecutionEvents.StartPackage() at Microsoft.SqlServer.IntegrationServices.Server.ISServerEx ec.ProjectOperator.StartPackage() at Microsoft.SqlServer.IntegrationServices.Server.ISServerEx ec.ProjectOperator.PerformOperation()

External component has thrown an exception.; at Microsoft.SqlServer.Dts.Runtime.DtsContainer.Execute (Connections connections, Variables variables, IDTSEvents100 events, IDTSLogging100 log, Object transaction) at Microsoft.SqlServer.IntegrationServices.Server.ISServerEx ec.ISServerExecutionEvents.StartPackage()

Answer 1

Hi @Andy,

Visual Studio 2019 Run64bitRunTime (True/False) setting is irrelevant for the SSIS run-time server. That setting is just for VS2019 on the developer workstation.

What is important is to set the TargetServerVersion setting in VS2019 to match your SSIS run-time server version, i.e. SQL Server instance version.

The SQL Server resides on a Windows server, which does not have Excel or any Office applications. It has Microsoft Access Database Engine 2016 (both 32 and 64 bits).

There is no need in Excel or MS Office on the SSIS run-time server. You need just Microsoft ACE OLEDB Provider 64-bit.

Let's check if it is installed by issuing the following T-SQL statement in SSMS on the SSIS run-time server:

EXEC master.sys.sp_MSset_oledb_prop;

You should see something along the following:

Obviously, you need to check if SSIS Excel Source connection is using MS ACE OLEDB Provider 16.0

Answer 2

Hi @Andy,

Edit the job step. Go to "Execution options". Try "Use 32 bit runtime" option.

Regards，

Zoe Hui

---

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