job failed message

Question

Post reply

job failed message

smattiko83

SSC-Addicted

Points: 487
More actions
December 2, 2019 at 3:42 pm

#3703477

Hi, this is the first time I've tried to schedule a job and I keep getting the error message below. I am using the admin account and checking the 32 bit box. Any ideas on how to remedy? Thanks.

Executed as user: NT Service\SQLAgent$BZMSSQLSERVER. Microsoft (R) SQL Server Execute Package Utility Version 14.0.2014.14 for 32-bit Copyright (C) 2017 Microsoft. All rights reserved.
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<c/> 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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

It looks as if you're trying to open an Excel workbook but another user or process already has it open.

John

smattiko83 SSC-Addicted Points: 487 More actions · Answer 2

Thanks. How is that possible? There is nothing else opened. Am I missing something?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

There is nothing else opened where? If the workbook is on a file share, it could be in use by anyone anywhere (depending on how tightly you define your share and NTFS permissions). Get your Windows people to help you identify who is using it - they may need to use special Windows internal tools to do so.

John

smattiko83 SSC-Addicted Points: 487 More actions · Answer 4

Could it be related to when I saved during the export wizard? I want to run a piece of sql code and have the results export to an excel file is my goal. Just not sure how.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

I don't know, because I can't see what you can see. Is changing the name of the file you're exporting to an option?

John

smattiko83 SSC-Addicted Points: 487 More actions · Answer 6

I'm not sure if I'm doing the steps correct. How would I have it run a piece of sql code and export the results? For this I went to the db, right clicked, went to task, export and then saved. I then went to job agent and scheduled using that saved task. Is that correct?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Sounds about right. But then you'll be exporting to the exact same file from your job, and if you still have that file open, it'll fail. If you don't still have it open, I don't know what to suggest. Maybe edit the package in Visual Studio and change the file name? Or you could use bcp and choose a fresh file each time.

John