none
"Failed to open package file ..." when modifying job step on remote server

    Question

  • I have SSIS package setup on a remote SQL Server to run on schedule via SQL Server Agent. It was setup initially by remote desktop-ing to the server and connecting to it via SSMS running on the same machine where SQL Server is running. Package source is "File System" and absolute path is specified (D:\Folder1\Package1.dtsx). There is just one step in the job - to run SSIS package. Everything works as it should.

    Now I want to modify this job - change number of retry on failure attempts. I can successfully connect to this SQL Server instance via SSMS from my developer workstation. I can then go to "Advanced Settings" for this job step and change "Retry attempts" to some other number. However, when I press "OK" I get this error message:

    TITLE: SSIS Execution Properties
    ------------------------------

    Failed to open package file "D:\Folder1\Package1.dtsx" due to error 0x80070003 "The system cannot find the path specified.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
     ({105912C7-6345-42B4-99B4-58566291867B})

    ------------------------------
    ADDITIONAL INFORMATION:

    Failed to open package file "D:\Folder1\Package1.dtsx" due to error 0x80070003 "The system cannot find the path specified.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
     ({105912C7-6345-42B4-99B4-58566291867B})

    Interestingly enough, in spite of this error message the number of retries is successfully changed.

    I understand it happens because SSMS tries to locate package on my local hard drive and the same folder doesn't exist on my development PC. It still doesn't make sense to me. Does SSMS not know I am accessing remote server? How come I don't get any errors when I just execute this job via SSMS when SSMS runs on my development PC?

    Tuesday, May 15, 2012 5:00 PM

Answers

  • Hi,

    SSMS is just a client tool and does not keep any awareness of the server you connect to (unless you register them with SSMS). You you try to run the job via SSMS, all it does it pass on the command to the server you are connected to. Jobs are stored in msdb, so msdb on that server knows to access the path of the SSIS package on that server itself. 

    This is expected & documented behavior of SSMS. However, if you are interested in making things clearer for your own benefit, you should always specify the SSIS package locations using UNC paths rather than absolute paths.


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    • Marked as answer by JoeSchmoe115 Tuesday, May 15, 2012 5:32 PM
    Tuesday, May 15, 2012 5:07 PM

All replies

  • Hi,

    SSMS is just a client tool and does not keep any awareness of the server you connect to (unless you register them with SSMS). You you try to run the job via SSMS, all it does it pass on the command to the server you are connected to. Jobs are stored in msdb, so msdb on that server knows to access the path of the SSIS package on that server itself. 

    This is expected & documented behavior of SSMS. However, if you are interested in making things clearer for your own benefit, you should always specify the SSIS package locations using UNC paths rather than absolute paths.


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    • Marked as answer by JoeSchmoe115 Tuesday, May 15, 2012 5:32 PM
    Tuesday, May 15, 2012 5:07 PM
  • Hi,

    SSMS is just a client tool and does not keep any awareness of the server you connect to (unless you register them with SSMS). You you try to run the job via SSMS, all it does it pass on the command to the server you are connected to. Jobs are stored in msdb, so msdb on that server knows to access the path of the SSIS package on that server itself. 

    This is expected & documented behavior of SSMS. However, if you are interested in making things clearer for your own benefit, you should always specify the SSIS package locations using UNC paths rather than absolute paths.


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    What you are saying makes sense. However, even using UNC paths would not be a universal solution when one computer has rights to access that path and another - doesn't.
    Tuesday, May 15, 2012 5:12 PM
  • What you are saying makes sense. However, even using UNC paths would not be a universal solution when one computer has rights to access that path and another - doesn't.
    Computers don't have rights - users do

    Chuck


    Tuesday, May 15, 2012 5:17 PM
  • What you are saying makes sense. However, even using UNC paths would not be a universal solution when one computer has rights to access that path and another - doesn't.

    Computers don't have rights - users do

    Chuck


    Correct, this is what I meant.
    Tuesday, May 15, 2012 5:19 PM
  • Then it works as expected.  A user who does not have rights to the location of the package should not be able to mess with the job.  I think what a lot of people forget is that SQL Agent is intended to be used by the DBA's, not random developers.  The fact that people are able to set up their own jobs indicates that the server is not set up securely.  People and services who should be impacting jobs will have the required permissions to access the file locations.

    Chuck


    Tuesday, May 15, 2012 5:23 PM
  • Then it works as expected.  A user who does not have rights to the location of the package should not be able to mess with the job.  I think what a lot of people forget is that SQL Agent is intended to be used by the DBA's, not random developers.  The fact that people are able to set up their own jobs indicates that the server is not set up securely.  People and services who should be impacting jobs will have the required permissions to access the file locations.

    Chuck


    In case described in OP it's not even a case of required permissions - SSIS location is referenced by local path on remote PC that cannot be accessed from any other PC, no matter what access permissions are.
    Tuesday, May 15, 2012 5:27 PM
  • Then it works as expected.  A user who does not have rights to the location of the package should not be able to mess with the job.  I think what a lot of people forget is that SQL Agent is intended to be used by the DBA's, not random developers.  The fact that people are able to set up their own jobs indicates that the server is not set up securely.  People and services who should be impacting jobs will have the required permissions to access the file locations.


    Chuck


    In case described in OP it's not even a case of required permissions - SSIS location is referenced by local path on remote PC that cannot be accessed from any other PC, no matter what access permissions are.

    Yes, but you've already got the part about the job being configured incorrectly figured out, I was commenting on your statement about permissions to a UNC path - and that it sounds like a non DBA has permissions to be modifying jobs.

    Chuck


    Tuesday, May 15, 2012 5:29 PM
  • What you are saying makes sense. However, even using UNC paths would not be a universal solution when one computer has rights to access that path and another - doesn't.

    Computers don't have rights - users do

    Chuck


    Correct, this is what I meant.

    That is precisely why rights are to be managed via Active Directory Security Groups, and not for individual user. I do Agree with your point that UNC is not a universal solution, because it will not take care of itself automatically when the job is migrated between environments.

    I implement the token based solution to mitigate the need to manually update the SSIS package path in the job when moving across environments. I am not sure if that's what you are looking for, but if you are, then this will help - $(ESCAPE_NONE(SRVR)) .

    This is to be used the job step setup script ;

    @command=N'/FILE "\\$(ESCAPE_NONE(SRVR))\Package\Test_Package.dtsx" /CHECKPOINTING OFF /REPORTING E', 

    Now, so long as you have a UNC path of each machine that follows the pattern \\MachineName\Package - this will work


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    Tuesday, May 15, 2012 5:30 PM