Error execute a SSIS Packed with SQL Agent

Question

Error execute a SSIS Packed with SQL Agent

Wolfgang 0

Hello, 
we have a very strange problem when executing an SSIS package with the SQL Agent.

The package has the following tasks for an Excel file:
1. Check file in the source path. 
2. Copy file to the destination path. 
3. Open file in the destination path. 
4. Rename first worksheet. 
5. Save File.  
6. Import file. 

When executed with the SQL Agent, tasks 1-5 are processed successfully.

An error is generated at step 6. If the package is executed in SMSS, all six tasks are completed successfully.

The permissions on the source and destination paths are the same; otherwise, the SQL Agent would not be able to copy, open, modify, and save the file.


Thanks in advance for your help.

SQL Server = 2017

Visual Studio = 2022 with the latest SSIS integration

grafik

on paths are the same; otherwise, the SQL Agent would not be able to copy, open, modify, and save the file.

Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2025-07-31T12:36:05.36+00:00

Exactly how is that step 6 implemented. Is the package itself reading the file, or are you using OPENROWSET or similar from SQL Server?

Where is the file located? On local disk, or on a file share?

How is the job running? Through a proxy or through the service account for SQL Server Agent?
Wolfgang 0 Reputation points

2025-07-31T13:23:56.98+00:00
Hello,

The job is executed by a proxy user.

The Excel-File is on a file share.

Changing the connection manager to SQLCMD (Select * from Sheet$) did not change anything, neither when running in SMSS nor in SQL Agent.
Wolfgang 0 Reputation points

2025-07-31T13:39:55.5666667+00:00

This picture is an addon to my previous post.
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2025-07-31T21:42:29.44+00:00

And this proxy user is a domain user who has access to the file share?

1 answer

Your answer

Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2025-07-31T12:36:05.36+00:00

Exactly how is that step 6 implemented. Is the package itself reading the file, or are you using OPENROWSET or similar from SQL Server?

Where is the file located? On local disk, or on a file share?

How is the job running? Through a proxy or through the service account for SQL Server Agent?
Wolfgang 0 Reputation points

2025-07-31T13:23:56.98+00:00

Hello,

The job is executed by a proxy user.

The Excel-File is on a file share.

Changing the connection manager to SQLCMD (Select * from Sheet$) did not change anything, neither when running in SMSS nor in SQL Agent.
Wolfgang 0 Reputation points

2025-07-31T13:39:55.5666667+00:00

This picture is an addon to my previous post.
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2025-07-31T21:42:29.44+00:00

And this proxy user is a domain user who has access to the file share?

Answer 1

Yitzhak Khabinsky 26,676

Hi @Wolfgang,

It is possible that you need to modify your Microsoft ACE Provider.

Your screen shot shows "Microsoft Excel 2007-2010".

Please try to modify it as one of the following options:

Microsoft Excel 2013
Microsoft Excel 2016

Also, please issue the following command on the SSIS run-time server in SSMS, and share a screen shot of it:

EXEC master.sys.sp_enum_oledb_providers;

Wolfgang 0 Reputation points

2025-08-01T06:39:14.46+00:00

Hi,

thank you for your effort to support us. Below is the result of the sp.
Wolfgang 0 Reputation points

2025-08-01T08:55:03.4466667+00:00

Hi,

thank you for supporting us. Below is the result of the sp.
Yitzhak Khabinsky 26,676 Reputation points

2025-08-01T11:29:09.0633333+00:00
Hi @Wolfgang,

The screen shot confirms that you have Microsoft ACE Provider versions 12.0 and 16.0. It confirmed my guess that you need to switch to "Microsoft Excel 2016" on the UI side. Such selection corresponds to Microsoft ACE Provider 16.0

Additionally, the following query will give you all warnings and errors in textual format. You just need to provide an operation_id parameter value from the failed SSIS package execution by running a report from the SSIS Catalog (all your screen shots are coming from it). Please provide its output in textual format.

SELECT TOP(1000) operation_id, message, package_name, event_name, message_source_name FROM SSISDB.CATALOG.EVENT_MESSAGES WHERE operation_id = 919176 -- your value goes here !!! AND event_name IN ('OnError', 'OnWarning') ORDER BY message_time DESC;

Share via

Error execute a SSIS Packed with SQL Agent

1 answer

Your answer