Save SSIS package and schedule job in SSMS

  • smattiko83

    SSC Enthusiast

    Points: 139

    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.

    Attachments:
    You must be logged in to view attached files.
  • Phil Parkin

    SSC Guru

    Points: 243862

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • smattiko83

    SSC Enthusiast

    Points: 139

    There is no error message with the time.

  • Phil Parkin

    SSC Guru

    Points: 243862

    I call this an error message.

    Annotation 2019-12-02 130511

    What do you call it?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • smattiko83

    SSC Enthusiast

    Points: 139

    That is not the issue. The job runs but is failing. The message you are looking at does not allow someone to schedule.

  • Phil Parkin

    SSC Guru

    Points: 243862

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • smattiko83

    SSC Enthusiast

    Points: 139

    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.

  • Mr. Brian Gale

    SSC-Insane

    Points: 22444

    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...

  • This was removed by the editor as SPAM

  • smattiko83

    SSC Enthusiast

    Points: 139

    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.

  • Phil Parkin

    SSC Guru

    Points: 243862

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • This was removed by the editor as SPAM

  • Phil Parkin

    SSC Guru

    Points: 243862

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • smattiko83

    SSC Enthusiast

    Points: 139

    Do you mean this?

    Attachments:
    You must be logged in to view attached files.
  • Phil Parkin

    SSC Guru

    Points: 243862

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 15 posts - 1 through 15 (of 17 total)

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