Share via

SSIS Package Fails in Validation (Excel Destination)

Alex (CSharper) 1 Reputation point
2021-10-01T16:02:52.623+00:00

Hi, I have been frustratingly trying to deal with a very odd problem. I created a package to handle the filling of an Excel file from a SQL query. I spent a couple days debugging, making sure it worked through manual execution, then I managed to get it working through SQL Server Agent on a job. After it was working perfectly fine (even when scheduled), we made a few folder changes. I updated the package to reflect this, only changing the file destinations as well as moving the existing files, just to make sure it wouldn't complain. The package is now failing without a single error. The following is all of the information I can find:

The Job History shows the following message for an error: 

Executed as user: NT Service\SQLSERVERAGENT. The step did not generate any output.  The return value was unknown.  The process exit code was -1066598274.  The step failed.

The job log that I also set up for more testing is empty every time.

The package log that I created, monitoring every single activity on every single step, shows very little insight. My first task, a file copy of the Excel template, runs perfectly fine. My Data Flow Task is where the issue lies. Here is the log data for the Data Flow Task: 

Data Flow Task,10/1/2021 10:37:28 AM,10/1/2021 10:37:28 AM,0,0x,Validating
Data Flow Task,10/1/2021 10:37:28 AM,10/1/2021 10:37:28 AM,0,0x,Data flow engine will call a component method. : 2 : Data Conversion : AcquireConnections
Data Flow Task,10/1/2021 10:37:28 AM,10/1/2021 10:37:28 AM,0,0x,Data flow engine has finished a call to a component method. : 2 : Data Conversion : AcquireConnections : 132775762489534398 : 132775762489534398
Data Flow Task,10/1/2021 10:37:28 AM,10/1/2021 10:37:28 AM,0,0x,Data flow engine will call a component method. : 2 : Data Conversion : Validate
Data Flow Task,10/1/2021 10:37:28 AM,10/1/2021 10:37:28 AM,0,0x,Data flow engine has finished a call to a component method. : 2 : Data Conversion : Validate : 132775762489846623 : 132775762489846623
Data Flow Task,10/1/2021 10:37:29 AM,10/1/2021 10:37:29 AM,0,0x,The Data Conversion spent 0 milliseconds in Validate.
Data Flow Task,10/1/2021 10:37:29 AM,10/1/2021 10:37:29 AM,0,0x,Data flow engine will call a component method. : 2 : Data Conversion : ReleaseConnections
Data Flow Task,10/1/2021 10:37:29 AM,10/1/2021 10:37:29 AM,0,0x,Data flow engine has finished a call to a component method. : 2 : Data Conversion : ReleaseConnections : 132775762490159161 : 132775762490159161
Data Flow Task,10/1/2021 10:37:29 AM,10/1/2021 10:37:29 AM,33,0x,Validating
Data Flow Task,10/1/2021 10:37:29 AM,10/1/2021 10:37:29 AM,0,0x,Data flow engine will call a component method. : 248 : Excel Destination : AcquireConnections
Data Flow Task,10/1/2021 10:37:29 AM,10/1/2021 10:37:29 AM,0,,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource(NULL, CLSCTX_INPROC_SERVER, ConnectionString: Provider=Microsoft.ACE.OLEDB.16.0;Data Source=REDACTED.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";,...)'.
Data Flow Task,10/1/2021 10:37:29 AM,10/1/2021 10:37:29 AM,0,,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
Data Flow Task,10/1/2021 10:37:29 AM,10/1/2021 10:37:29 AM,0,,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.

That is the final line of the log. No error, it just stops mid validation for no reason. The server has the appropriate installs (remember, it was working on this very same server before with no problems). I can even use the dtexec utility and it runs perfectly fine from the server. The file permissions have been triple checked and I even gave the server full control to the folder just to see if it would do anything at all. Can anyone please help with this?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,494 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,681 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 41,456 Reputation points
    2021-10-04T03:02:34.863+00:00

    Hi @Alex (CSharper) ,

    Have you tried to create a simple package and then schedule and run via SQL Agent to see if it will work or not?

    Are you sure account executing package from job has required access to perform the required operations? Such as the file read/write permission.

    create-a-sql-server-agent-proxy

    I did some online research you may take a reference of this blog to see if it is helpful.

    Regards，

    Zoe

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

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.