question

AlexCSharper-6173 avatar image
0 Votes"
AlexCSharper-6173 asked AlexCSharper-6173 commented

SSIS Package Fails in Validation (Excel Destination)

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-generalsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered AlexCSharper-6173 commented

Hi @AlexCSharper-6173,

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is a production server it is running on and there are several other packages in jobs running just fine, yes.

The account in question does have the proper access defined as it is using other folders within the directory it operates in and is able to create those files just fine.

To add, with the way we currently have user access configured, proxy users are not an option.

0 Votes 0 ·