SQL 2005 Agent Job Error

Question

SQL 2005 Agent Job Error

uzi yona

SSC Journeyman

Points: 97
More actions
December 2, 2008 at 3:05 am

#154971

Hi,
I have created a SQL 2005 Job to run SSIS package and i am getting the following error:
Executed as user: CODE1\ILQHFAATC1-sys5. ...e Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:32:43 AM Error: 2008-12-02 10:32:43.33 Code: 0xC0011007 Source: {05EBD81F-724F-4631-ADA7-2BEE12811E3E} 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: 2008-12-02 10:32:43.33 Code: 0xC0011002 Source: {05EBD81F-724F-4631-ADA7-2BEE12811E3E} Description: Failed to open package file "D:\My Documents\Visual Studio 2005\Projects\DOORS_DB_Inventory\DOORS_DB_Inventory\Package.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 specifi. The step failed.
The Job is using Package.dtsx file and run as SQL Agent Service account.
Any ideas why?
Thanks in advance.
Uzi

Viewing 4 posts - 1 through 3 (of 3 total)

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I've gotten this error when I've saved a package with the Protection Level set to the default EncryptAllWithUserKey, which means only I can execute the package. I changed the Protection Level to EncryptAllWithPassword and the SQL Agent account was able to execute the package in a job when I supplied the password in the job step.

Greg

uzi yona SSC Journeyman Points: 97 More actions · Answer 2

Hi Greg,

Can you elaborate you r solution?

Whare can I set the Protection Level to the default EncryptAllWithUserKey?

Where Do i need to do it in the Job?

I always use windows authentication so I am not familiar with that.

Thanks for your reply.

Uzi

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

When you open a package I Business Intelligence Development Studio, Protection Level is shown in the package properties. That's where you can change it. By default, it's always set to EncryptAllWithUserKey, which means the person who last edited the package is the only one who can execute the package. I set it to EncryptAllWithPassword, which will allow execution of the package by anyone who supplies the correct password. Because a package executed in a job is run by the SQL Agent account, not you, EncryptAllWithUserKey will prevent the package from running. Books On Line has a good description of the protection levels.

So, you change the Protection Level in BIDS, not in the job. Assuming the job step type is set to Integration Services, there's a place where you can enter the password you set when you saved the package in BIDS.

Hope this helps.

Greg