DTS Package reports error when executed from an ASP page

I'm not sure that this is the correct place to ask this but please bear with me. I am trying to execute a DTS package from an ASP page on the web server but the package fails and returns the error "invalid file path".

The package connects to a remote folder on the LAN and uploads a couple of foxpro tables to SQL Server. The package runs without errors when executed from Enterprise Manager.

I have an inkling the problem is to do with permissions but I'm not sure. Is it true that when run from a web page, the DTS is executed by the webserver? If so, how can I modify the access permissions of the remote folder so as to grant IUSR_WebServerName Read and Write access?

Any help is much appreciated.

Yes it is true that the DTS package will be executed by the Webserver, with whatever permissions that service account has. I am sorry, but I do not know enough about the web server end of the operation to tell you how to give the webserver access to that folder.

Donald Farmer

|||

Thanks for the reply. I have solved the problem by using COM objects to wrap the DTS and runing it from a stored procedure.

|||

Thanks. Good to hear you have it working.

Dts Package Execute Problem

Hi all,

I run a dts from my aspx page. It works on my computer (win xp) fine, but it does not start to work when i deploy project to server 2003.

In my computer sql 2000 is installed but it is not installed on win2003 server. So my error's cause is this. My error is :

Retrieving the COM class factory for component with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} failed due to the following error: 80040154.

I could not solve it. I look atthis topic but i got the same errors.

Can i run dts without installing sql server on win2003 machine anyway?

Extra "dtspkg.rll" file and files onthis topic must be registered.

Regards..

DTS issues...

When executing a DTS Package from an ASP page, the steps of the package are executed in the wrong order, but it's the same wrong order every time. Any ideas?Cross post...

http://www.dbforums.com/t993006.html

Stay with your original thread...

DTS issues...

When executing a DTS Package from an ASP page, the steps of the package are executed in the wrong order, but it's the same wrong order every time. Any ideas?What doe sthat mean?

They could thread out, depending on how you coded it?

Where in Jersey?

http://members.tripod.com/~mugsy11/heyrube.html|||how does it run locally?|||Meaning that the steps in the package are coded correctly. I create a table, then populate the new table, then export to an excel file. Running it from Enterprise Manager works like a champ every time, but when running it from an ASP page, it always tries to populate before creating.

Now after trying to tweek a few things, my code is screwy and I'm get server errors. I'll get it sorted out and post the exact errors I was getting.|||Does it have to be excel?

Can it be csv?

How about a sproc...

Even if it needed to be a xls, you could use a com object I guess...

I've seen DTS do some weird stuff...especially if this is a versioned package...

Can you recreate a brand new package and try it?

I know this is a stupid question, but you do have workflow steps in there...right?

I'd still go with a sproc....|||Originally posted by Brett Kaiser
Does it have to be excel?

Can it be csv?

How about a sproc...

Even if it needed to be a xls, you could use a com object I guess...

I've seen DTS do some weird stuff...especially if this is a versioned package...

Can you recreate a brand new package and try it?

I know this is a stupid question, but you do have workflow steps in there...right?

I'd still go with a sproc....

Yeah, the workflow steps are in there, and you just answered my next question. I was thinking about calling a sproc to execute it, so now that you said that, I'm going to give that a try.

And I'm in the south Jersey area by the way. Erial to be exact....about 5 miles south of Deptford.|||When you said south...you ain't lion

How far to AC?

http://www.mapquest.com/maps/map.adp?country=US&countryid=250&addtohistory=&address=&city=Erial+&state=NJ&zipcode=&submit=Get+Map

Flyer fan?

Come on, you can admit it...

Go Devils...|||Oh, and let me ask...

Do you or someone you know own a confederate flag?|||Originally posted by Brett Kaiser
When you said south...you ain't lion

How far to AC?

http://www.mapquest.com/maps/map.adp?country=US&countryid=250&addtohistory=&address=&city=Erial+&state=NJ&zipcode=&submit=Get+Map

Flyer fan?

Come on, you can admit it...

Go Devils...

I'm 20 minutes or so from AC depending on how heavy my foot is that day. And I'm not a fan for ANY Philly team. I'm from the midwest originally, so that's where my loyalties are. And as far as hockey goes, I'm more of a Redwings fan than anything I guess, but the Rangers are pseudo-cool.|||A transplant TO NJ?

There's a switch...

btw, in your sproc I'd use xp_cmdshell and ftp to get the data out...

How/ why are you creating this table?

What the data source?|||Yeah. Was in the Navy for 9 years and got dropped off on the east coast and just stayed. It's a long story. And every long story involves a woman, so I'll leave it at that for now.

Every month, our affiliates require a report(xls) that contains referrals, items purchased, costs and prices, etc. It's a different query for every affiliate and until recently, the person in charge of this was running each query in QA and then saving the results to an xls file. When this duty was passed to me, I quickly decided that the was NO way that I was going to do it all manually like that. So I put together the DTS package that works just fine for me because I have all the permission and rights that a person could have, so I would just run the package once a month.

Soon after, the powers that be, who also have large plush offices and are technical idiots, decided that they wanted to be able to check this data when and where ever they want to, so I added a link to our intranet, and that's where my problem started.|||Yeah, I set that up for 1 project...

Each report has it's own sproc...

Each report is in a report table which contains report names, sproc names, if it's batch or online (batch save it to a file location, online is saved to their local through a browswer)

The browser executes a job when they select a report that runs the sproc.

That's important becuase we found if the sproc took a whil, the browser would hang...basically started another independant thread...

The report sprocs would create csv files with a header...like..

Select '"'+LTrim(RTrim(IsNull(SignOffEntity,'')))+'"'
+',"'+LTrim(RTrim(IsNull(LedgerSumAmount,'')))+'"'
+',"'+LTrim(RTrim(IsNull(LedgerNumOfRows,'')))+'"'
From
( Select 0 As SQLGroup, 'ATS Sign-Off Entity' As SignOffEntity
, 'Ledger Balance' As LedgerSumAmount, 'Ledger Count' As LedgerNumOfRows
Union All
Select 1 As SQLGroup, a.SignOffEntity
, Convert(Char(15),Sum(IsNull(b.Amount,0))) As LedgerSumAmount
, Convert(Char(15),Count(b.Entity)) As LedgerNumOfRows
From ATS_SignOff_Entity a (NoLock)
Left Join (Select Entity, IsNull(Amount,0) As Amount
From Ledger_Detail c (NoLock)
Where Account_Type In ('4', '5', '6', '7')
Union All
Select Entity, IsNull(Convert(money,BOY),0) As Amount
From tblAcct_LedgerBalance d (NoLock)) b
On a.Entity = b.Entity
Group by a.SignOffEntity
Union All
Select 2 As SQLGroup, 'TOTAL'
, Convert(Char(15),Sum(IsNull(b.Amount,0))) As LedgerSumAmount
, Convert(Char(15),Count(b.Entity)) As LedgerNumOfRows
From ATS_SignOff_Entity a (NoLock)
Left Join (Select Entity, IsNull(Amount,0) As Amount
From Ledger_Detail c (NoLock)
Where Account_Type In ('4', '5', '6', '7')
Union All
Select Entity, IsNull(Convert(money,BOY),0) As Amount
From tblAcct_LedgerBalance d ) b
On a.Entity = b.Entity
) As A

Notice that it's just 1 column, because all of the columns are concatenated...notice too the column conversions to char where needed...

Also notice the trailer or total row...they're kept in the correct order by the SQLGroup Column...