?
Solved

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

Posted on 2008-06-18
2
Medium Priority
?
610 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 13

Accepted Solution

by:
rickchild earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

752 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