DTS failing when called from .ASP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DTS failing when called from .ASP

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2001-12-18 : 04:19:48

Hi there,

I have a DTS package that is fired from .asp page which exports data from the Data server to the web server. This works fine when I execute the package in enterprise manager - but when using my SQL login/password to connect it fails with the following event log (on web server):

The description for Event ID ( 80 ) in Source ( DataTransformationServices ) could not be found. It contains the following insertion string(s):

Package Name: H_EXPORT
Package Description: DTS package description
Package ID: {941620B9-C547-4565-BAC3-AE688F803EF5}
Package Version: {B2DEC642-9CB5-46AB-86D1-38C2D79A2A90}
Package Execution Lineage: {FC21FF18-AF7E-4AAF-80EE-96F12D549980}
Executed On: GLOBAL_NT
Executed By: IUSR_GLOBAL_NT
Execution Started: 18/12/01 17:20:27
Execution Completed: 18/12/01 17:20:27

Package Steps execution information:

Step 'Copy Data from tblProviders to \\GLOBAL_NT\D$\plus\export\tblProviders.csv Step' failed

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: Access is denied.

Step Error code: 80040E21
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0

I believe this to be permissioned based - but there is no authentation to select when writing to the .csv text file. I even gave the IUSR_GLOBAL_NT account full access to the files to overwrite just to see if it would work.

I changed the DTS to export only to itself (the data server) and it worked - however I need to export accross a network...is this possible?

Any suggestions appreciated.

Matt

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2001-12-18 : 04:34:05

I didn't spend much time on your problem, but I'm concerned about the Admin share (\\GLOBAL_NT\D$\). Can you make it a normal share, set appropriate access permissions and try again. You might want to read the following article that talks about such problems:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074

--
HTH,
Vyas
Check out my SQL Server site @
http://vyaskn.tripod.com

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2001-12-19 : 19:17:50

Thanks for your post Vyas - I did find that the article useful but it referred to scheduled jobs and not directly executing the DTS package from .asp

I finally gave up on trying to execute the package from a different machine (broken man!) and designed the package to export to the csv file locally rather than accross the network. Now when I execute the package from .asp I get a success message - but when I view the text file there is no data. I check to see my package is working correctly by executing it from the Enterprise manager and as expected data is exported to the text file.

I am perplexed as to how I am getting success messages but no data is being exported when called from .asp. The code I am using is pretty much identitical to: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q252987

The application works fine on our local production machines.

Has anyone had a problem similar to this?

cheers - matt

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-12-19 : 19:32:06

Matt,

I had a similar problem running DTS packages in distributed environments.

Can the Web server see the DB... Yes
Can the DB see the Web Server... NO

To get around this.. Save your DTS as a VB file!!!

After you clean the code up (Watch for redundant objects, file locations etc...), place into into its own class (It comes out as a BAS) and then simply call that class from your ASP. It works like a charm...

Effectively it is creating the package in memory and executing..
It will not raise errors caused during execution so you have to check (They supply that code when it is scripted)

HTH

DavidM

"Why are you crying?"

"Because I love a Microsoft product"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-19 : 20:07:48

quote:
Thanks for your post Vyas - I did find that the article useful but it referred to scheduled jobs and not directly executing the DTS package from .asp

What about creating a job out of the DTS package, then run sp_start_job on it as an ASP call? (connObj.Execute "sp_start_job 'myDTSJob'")

If needed, an xp_cmdshell 'copy c:\textfile.txt \\webserver\webfolder\textfile.txt' command could then move the text file to the webserver. Since DTS doesn't seem able to write directly to the web server I don't think this will work either, but who knows?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources