Save SSIS package and schedule job in SSMS

0

I am trying to run sql code and have the results export to an excel file. I've done the steps below to save the task and schedule the job but I keep getting job failed with the error below. What am I missing? Thanks.

Started: 9:48:28 AM
Error: 2019-12-02 09:48:29.50
Code: 0xC0202009
Source: TEST Connection manager "DestinationConnectionExcel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.
Source: "Microsoft JET Database Engine"
Hresult: 0x80004005
Description: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user or you need permission to view its data.". End Error
Error: 2019-12-02 09:48:29.50
Code: 0xC00291EC
Source: Preparation SQL Task 1 Execute SQL Task
Description: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:48:28 AM Finished: 9:48:29 AM Elapsed: 0.641 seconds. The package execution failed. The step failed.

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

sql-server
asked on Stack Overflow Dec 2, 2019 by Sm85

1 Answer

0

Error message is pretty clear: "It is already opened exclusively by another user or you need permission to view its data."

  • check if the file exists on SSIS server, because location "C:...\Administator\Desktop\Text.xls" may be on your development workstation, not the server. It is better to use UNC paths.
  • check privileges, if the SQL Server Agent service account can access this file.
  • check, if the file is open in excel, close all excel instances.
answered on Stack Overflow Dec 2, 2019 by Piotr

User contributions licensed under CC BY-SA 3.0