I'm looking at a DTS Package here that is launched from a Stored Procedure. The DTS Package writes data to an Excel spreadsheet. For this it uses a JET connection (apparently!)
The problem is this: When I go in through Enterprise Manager, and I Execute the DTS Package by simply clicking on it, it runs fine. It creates the desired Excel spreadsheet, and it populates it.
However, if the DTS packages is lanuched through a Stored Procedure, the JET engine throws a wobbly. It reports error 80004005, with a "help context" (whatever that may be) of 5000000. To be extra helpful, it reports that this is an "unspecified error".
Googled this to death but I'm seeing nothing that strikes me as particularly valid for my situation. In fact, it looks like the same symptoms could result from various different causes. However, I think I can eliminate a few:
- I don't think there is a problem with DTS as I can run the package manually, and it succeeds.
- I don't think there is a problem with the JET Engine per se, for the same reason.
- It isn't a case of having mis-spelled the name of the DTS package. In fact, the DTS Package produces a log file. From the Log file it is clear that the DTS Package is found and executed, and all the steps are visited in the correct order. Except, they then fail. As all steps are designed to continue after completion, regardless of their status, the DTS package as a whole succeeds:
The execution of the following DTS Package succeeded:
Package Name: [name removed to protect the innocent]
Package Description: Create Excel Extracts for [yada, yada]
Package ID: {C7DCF808-6D75-4C1D-9BE2-5
6B3E143C27
3}
Package Version: {F90DE123-4396-4937-BA9E-E
CF91953762
0}
Package Execution Lineage: {B74403F4-6318-4DC1-8F03-8
8086A2C64C
6}
Executed On: [Our server here] (and yes, I verified that this is correct)
Executed By: [I can confirm that this is the user that I would like to see here]
Execution Started: 18/06/2008 12:10:01
Execution Completed: 18/06/2008 12:10:08
Total Execution Time: 7.093 seconds
But this is a typical step from that package:
Package Steps execution information:
Step 'DTSStep_DTSExecuteSQLTask
_1' failed
Step Error Source: Microsoft JET Database Engine
Step Error Description:Unspecified error
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5000000
Step Execution Started: 18/06/2008 12:10:01
Step Execution Completed: 18/06/2008 12:10:01
Total Step Execution Time: 0.016 seconds
Progress count in Step: 0
There also does not appear to be a problem with Active X itself, as one of the first steps in the DTS package creates an instance of Scripting.FileSystemObject
which is then used to delete any existing copy of the spreadsheet, and that step succeeds without an error, and it does delete the file in question.
The Stored Procedure executes the DTS Package as follows:
EXEC @iErrorValue = sp_OAMethod @iPackageObject,
'LoadFromSqlServer',
NULL,
@ServerName = @ServerName,
@ServerUserName = @ServerUserName,
@PackageName = @PackageName,
@Flags = 256, -- Use Windows Authentication to connect to an instance of SQL Server.
@PackagePassword = '',
@ServerPassword = @ServerPassword
I also tried running this with @Flags set to 0, and also for a specific ServerUserName and Password, both for @Flags = 0 and @Flags = 256, but every time it causes the same error.
At this stage I'd be inclined to think that this would be a permissions and/or privileges issue. But I tried writing the Excel spreadsheet to the %TEMP% folder (resolving the name to the actual physical locationl, of course) in the hope that there should be no issues with that folder, and I have tried giving various users, including, in the end, "Everyone" full access to the target folder. All to no avail.
Please help me before I start screaming and I create an unholy scene here in the office that will dwarf the following:
http://www.youtube.com/watch?v=7Sq-HYGfnIoMay the FSM bless you and touch you with its noodly appendage.
