April 19, 2010 at 10:03 am
I have an SSIS package which executes perfectly in BIDS. When I schedule this SSIS package to run and initiate the scheduled job via the schedule or manually kicking off the scheduled job I get the following job failure error 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... Failed to open package file "C:\My package location..." due to error 0x800700005 "Access is denied". 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...
Any ideas/suggestions will be welcomed. -- Note the file name is correct.
April 19, 2010 at 10:22 am
Access denied is usually just that. Where is the file in relation to the SQL Server trying to run it, what I mean is whether the file is on the local file system? Does the user that SQL Agent login as have access to that location AND file and if it isn't local the share that is used to access it.
I think this is one of the single most common questions asked in relation to SSIS and 99.9% of the time it is permission differences between you and the server.
CEWII
April 19, 2010 at 10:43 am
Thanks for your response -- now I took your answer to mean I need to provide access to the folder the dtsx package is located in to the Server or SQL Server Agent. I provided the access on the folder but I am still getting the same error. I gave the agent the access to read and execute.
Don't mean to be thick but am I missing something here do I need to provide access in some other way?
April 19, 2010 at 12:32 pm
I cant answer the question yet..
Is the package file located on the same server as the SQL Server?
CEWII
April 19, 2010 at 12:34 pm
Yes
April 19, 2010 at 12:50 pm
Ok, you have verified that the login used by SQL Agent has access not only to the path the package resides at but the package itself. You might consider stopping and starting the agent just to be sure it is using that login. You might also verify exactly what login is being used, I got screwed on that once, networking changed it and didn't tell me.. It only happened once..
You might give it full access and then scale back later.
CEWII
April 19, 2010 at 1:07 pm
Would you know how to check the login being used for SQL Server Agent. My network admin is not very SQL Server literate. When I look in security in the logins folder I see one for sa-sqlagent but not sure how to confirm this is the one being used.
By the way I did stop and restart the SSA but still got the same error after running package.
April 19, 2010 at 1:10 pm
One other note I am using a copy of the solution to schedule -- sorry I should have mentioned that before.
April 19, 2010 at 1:49 pm
I don't view using a copy of the solution as important, but you might make sure that the copy was good.
As far as the verification.
In the Control Panel under Administrative Tools you will find the Services applet. Open is and scroll down to "SQL Server Agent", double click on it.. Go to the "Log On" tab. There you will see exactly what user is used to login..
Also look at the service "SQL Server" and "SQL Server Integration Services" as well.
CEWII
April 19, 2010 at 2:21 pm
I really appreciate all your assistance on this -- I have checked which login is the one for the SQL Agent and have given it full permissions and access to the folder.
When I manually start the job I am still getting the error.
More info: My package has a parent package which calls a child package -- the parent package has logic which it performs before calling the child package.
I have verified that access to all packages is given to the SQL Agent login.
Again really appreciate the help. Your thoughts...
April 19, 2010 at 2:57 pm
So how is the parent package calling the child package. Also you said you know the filename is correct, is it hard coded?
Are the paths on your local the same as the server?
CEWII
April 19, 2010 at 4:42 pm
Hi
Not sure if this helps, but when I had to schedule the job earlier i had same errors but mine was related to the Encryption and security in the package. i.e. the type of encryption used.. you may want to check that if not already
Gud luck
April 20, 2010 at 7:03 am
The parent package is calling the child package via an Execute package task. I know the file name is correct because when setting up the job I map to the dtsx package via the step executing the job. Everything is being run from the server i.e. the Job & packages. I am not on a seperate local machine.
April 20, 2010 at 7:05 am
What did you change the encryption to and from what. I have mine set to EncryptSensitiveWithUserKey.
May 1, 2010 at 7:19 am
Hi Mark
I am so sorry for belated reply. I had not checked my emails..
What I did was as follows -
Saved a copy of the package with the default security (i.e. encrypt sensitive with a key - can't remember the exact name just the default one anyway)
copied the package to the server where it was going to be scheduled to run
opened the package in the server locally and changed the security to "Encrypt sensitive with a key" where you can provide the password. This was done on both Parent and child package...
saved the packed and tried executing it....
This worked... However when the job was scheduled i did have to provide the password if it had anything to do with the package setting in the job otherwise it would not allow me to edit the package details... please let me know if you need further info and i will try.. but this is all i did
cheers & apologies again
Vani
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply