Solved

Launching a DTS package through a Stored Procedure and writing to Excel

Posted on 2008-06-18
2
605 Views
Last Modified: 2013-11-30
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-56B3E143C273}
Package Version: {F90DE123-4396-4937-BA9E-ECF919537620}
Package Execution Lineage: {B74403F4-6318-4DC1-8F03-88086A2C64C6}
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-HYGfnIo

May the FSM bless you and touch you with its noodly appendage.
0
Comment
Question by:WernerVonBraun
2 Comments
 
LVL 13

Accepted Solution

by:
rickchild earned 500 total points
ID: 21812035
It does sound like permissions.  

How is the stored procedure being run, and who is running it, the Agent or a person?
If the agent is running it from a job, check that the owner of the job has permissions.
If the SP is being run by an application, what account is being used here?


Also which user account is running your "MSSQLSERVER" Service in Windows?
We would use a service account here for the Services, which would be given access to write to the folder.
0
 
LVL 4

Author Comment

by:WernerVonBraun
ID: 21812233
So close!

I had actually stopped and started the SQLServerAgent service. But not the MSSQLSERVER one. I did that, and Robert turned out to be my mother's brother.

You're a star.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question