Save SSIS package and schedule job in SSMS
-
Hi, I'm trying to automate running code and exporting to excel. I've attached the steps I've taken but I keep getting the job failed message with errors. Any suggestions on what I'm doing wrong? Thanks.
-
Presumably you understand the error message? SQL Server thinks that you're scheduling the job in the past. What timezone is the server in? Is the server date/time correct?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
There is no error message with the time.
-
I call this an error message.
What do you call it?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
That is not the issue. The job runs but is failing. The message you are looking at does not allow someone to schedule.
- smattiko83 wrote:
That is not the issue. The job runs but is failing. The message you are looking at does not allow someone to schedule.
You expect us to diagnose a job failure without providing the error text? It could be one of hundreds of reasons.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I'd like to know if that is the proper way to set it up. Message is below.
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.
-
Not meaning to hijack the thread from Phil, but the error is clearly stated in the last bit of errors you posted:
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
Reading this, I have 2 theories:
1 - the file is open or not available to the SQL Server Agent Account
2 - you provided the path to the file on a network drive using the drive letter
If the SSIS package succeeds when you run it, then either of the above could be the case, or possibly other issues...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
This was removed by the editor as SPAM
-
I do not have any excel files open. Where do I look to show what is wrong? I am logged in using the admin account and the letter is directly on the server.
- smattiko83 wrote:
I do not have any excel files open. Where do I look to show what is wrong? I am logged in using the admin account and the letter is directly on the server.
What you are logged in as is not important here. The important thing is the SQL Server Agent account. Is this a domain account or a system account? Does the account have access to local drives on the server.
I suggest moving the file to a path which is likely to be more accessible & trying again. Maybe even create c:\Excel (on the server) & move it there, just for testing purposes.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
This was removed by the editor as SPAM
-
I am so sorry. I clicked on Report by mistake, instead of Quote.
If you look at the list Services running on the server, in the Log On As column, you will see the context of the SQL Agent service. Is it in the form domain\username?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Do you mean this?
-
Yes, that is not a domain account. Does that local account have the necessary rights to create files on local drives?
For the purposes of testing (and don't forget to change this back afterwards!), you could try adding that user to the Local Administrators group on the server and re-running the package. If it runs successfully, you will know for sure that this is a permissions issue.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply