January 26, 2011 at 9:23 am
Hi All,
I'm attempting to migrate some old DTS packages to SQL Server 2005. Some of these include ActiveX tasks that use the "Excel.Application" object, and the migration wizard seems to have just copied these "as is". The packages are stored in the file system, and when I run them from BIDS they work fine, but I'm getting errors trying to run as a SQLAgent job.
The error message is quite long and in the form:
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: 2011-01-26 16:06:50.61 Code: 0xC0011002 Source: {5DE953A2-1A06-4EDC-8FAE-01DEDF4AD18A} Description: Failed to open package file ", then the path and filename it's trying to run, followed by " due to error 0x80070005 "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 s... The package could not be f... The step failed
I'm guessing that this is something to do with permissions? The package protection level is EncryptAllwithPassword and the same password is set in the verification tab of the SQL Server Agent job. Can anyone suggest a solution - I don't really want to rewrite the packages, some of them are really complex. As an experiment I tried to re-code a test package as a script task, but got the same sort of error. Is it because of the "Excel.Application" object?
January 26, 2011 at 9:38 am
Provided the password is correct.. Does the login SQL is using to exec the package have access to the location of the package? Is it on a local drive or a UNC path? Is SQL Agent logging in with a user account or one of the system ones?
CEWII
January 26, 2011 at 9:46 am
The package is on a local drive and both it and the folder it's in are shared by everybody. I don't know about the SQL Agent, all I've done is add a new job the same as I've always done before - how can I find out?
January 26, 2011 at 9:53 am
John Hoare-150006 (1/26/2011)
The package is on a local drive and both it and the folder it's in are shared by everybody. I don't know about the SQL Agent, all I've done is add a new job the same as I've always done before - how can I find out?
You can use Administrative Tools|Services to see what account SQL and SQL Agent are using to log in.
You mention shared to Everyone.. That raises a question, you state the package in on the local drive, do you reference it by drive letter or by a UNC path? Keep in mind that file level security is on top of any share security, ie: a particular user has access to a share but not to a particular file, they won't have access. Also if SQL is not logging in as a user then a UNC path can't work.
Security can be tricky sometimes, especially when we are talking about multiple different kinds..
CEWII
January 27, 2011 at 9:49 am
I've just returned to this problem having done a little more testing and am becoming suspicious of the Excel.Application object. I have since migrated some other [SSIS] packages containing ActiveX script tasks and they work perfectly when run as a SQL Server Agent job, which to me seems to indicate it's not a permissions problem. If I take one of these scripts and insert the line 'set xl = CreateObject("Excel.Application")', it immediately errors when run as a SQL Server Agent job, although it still runs from BIDS.
January 27, 2011 at 9:59 am
64 or 32-bit? The "access denied" may not be the true problem and is screening the real one.
CEWII
January 27, 2011 at 10:08 am
Sorry, I left off the important bit, I get a different error now when I add the extra line "Retrieving the file name for a component failed with error code 0x0475618C"
January 27, 2011 at 10:11 am
32 or 64-bit?
Also, it isn't real often I google an error code and get NOTHING..
Does this run at all in BIDS? I didn't see where you stated one way or the other..
CEWII
January 27, 2011 at 10:18 am
32 bit I think, and I couldn't find the error code either! It runs perfectly in BIDS.
I'll try another example (that runs OK in BIDS) tomorrow to make sure that the spreadsheets it's referencing actually get changed. I'm reasonably sure they did before, but I've spent so long now trying to sort this out I can't be certain.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply