November 5, 2018 at 7:56 am
I have a package which I have to schedule through SQL Agent. Just a simple package which reads the data from Excel into a table in SQL Server.
There are 4 users involved
User A: who created a package who also has a permission on a folder where the source file is
User B: myself who has access to the folder where the solution file is but no access to DB servers and no access to the folder where the source file is.
User C: my admin account who has access to all DBs but not the folder where the solution file is nor user C has access to the folder where the source file is
User D: service account for SQL agent
This is what I did:
User B moved the file to user C so user C can create a job.
User C created a job and specified himself as an owner of that job
User C scheduled a job but when the job runs, it fails because the error says
Executed as user: User D. Microsoft (R) SQL Server Execute Package Utility Version 13.0.5153.0 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 8:10:06 AM Error: 2018-11-05 08:10:06.70 Code: 0xC0011007 Source: {A576F857-AE93-4432-99FD-F459C79E5133} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2018-11-05 08:10:06.70 Code: 0xC0011002 Source: {A576F857-AE93-4432-99FD-F459C79E5133} Description: Failed to open package file "C:\Users\UserC\Documents\Phones.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file name when calling LoadPackage or the specified XML file has an incorrect format. End Error Could not load package "C:\Users\UserC\Documents\Phones.dtsx" because of error 0xC0011002. Description: Failed to open package file "C:\Users\UserC\Documents\Phones.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file name when calling LoadPackage or the specified XML file has an incorrect format. Source: {A576F857-AE93-4432-99FD-F459C79E5133} Started: 8:10:06 AM Finished: 8:10:06 AM Elapsed: 0.093 seconds. The package could not be found. The step failed.
Questions:
Is the job failing because user D doesn't have a permission on C:\Users\UserC\Documents\Phones.dtsx?
Concern:
It doesn't matter who created this package or who has access to the source file right? Because SQL Agent is responsible for scheduling the job and not what's inside the package? Just trying to understand it better.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
November 5, 2018 at 10:24 am
This error always happens when you create a job for an SSIS package using a remote session of SSMS. The error is actually being thrown by SSMS, as SSMS can not find the package. You can safely ignore this error message, if the job is created as you expect.
November 5, 2018 at 11:12 am
crow1969 - Monday, November 5, 2018 10:24 AMThis error always happens when you create a job for an SSIS package using a remote session of SSMS. The error is actually being thrown by SSMS, as SSMS can not find the package. You can safely ignore this error message, if the job is created as you expect.
Looks like the error is thrown at runtime by dtexec.exe. If that's an SSMS error, it's a very misleading message.
My suspicion is that those paths do not exist on the server.
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
November 5, 2018 at 12:17 pm
That path doesn't exist on the server. We normally don't create any packages on the server. We use some network location where all the packages are. I was thinking about deploying the package on SSIS catalog and then schedule it. I am guessing I won't run into any permission issues.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply