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 Errors

I have created several DTS packages that run if I execute them imidiatly, but error out if scheduled with the following error...

Executed as user: Domain\Username. DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.Most likely it's that it doesn't have access to the drive...

You need to set DTS up so that when you manually run, it will find the same location on the server.

When you run, it runs under the context of your client, when scheduled it runs under the context of the agent id.

Make sure there is the same directory stucture on the server as you have (or vica versa) and make sure to copy the file there...|||Both are being run from the same server.|||What user is the owner of the job. That user may not have rights to execute the DTS package. Also check who the owner of the DTS package is?|||Already verified those and they are good.
The error states which user it is using and it is the same user name that SQL agent is started with. and it has all needed privalges that I can see.|||How did you create the job? Did you right click the job and select schedule?|||Yes|||Is the server SQL 7??|||No, it is SQL server 2000 Enterprise.|||Connections in the package are good? Anything dynamic going on?? Did you get the error message from the job logs??|||I get the same error on all my scheduled DTS jobs, including a test job I created that drops a test table. I find the error in the history of the job after it has run.|||Sorry running out of ideas. Is the owner of the job SA?? not the sql agent|||nope.|||Can you log onto the machine that actually runs the package and execute the package from there? Turned out to be the trick for me -- if I executed from nearly anywhere else there was a DSN set up to map the DNS name to a connection. Just not on the box itself, so EM could execute the package anywhere but on the actual box. I know sounds odd, but it's true...

DTS Package - Append Results to Text File

I have created a DTS package which transfers data from a SQL 2000
server table to a text file. Each time the package is executed the
data in the text file is replace with the new data from the current
execution of the package. Can DTS append new text to a text file and
not replace it each time the package executes. Is there an option or
seeting for this? I havent seen one. Can anyone help me out on this.
Thank you,

Brett"bdwgarth" <bwalke@.lbrspec.com> wrote in message
news:1105716432.160263.235590@.c13g2000cwb.googlegr oups.com...
>I have created a DTS package which transfers data from a SQL 2000
> server table to a text file. Each time the package is executed the
> data in the text file is replace with the new data from the current
> execution of the package. Can DTS append new text to a text file and
> not replace it each time the package executes. Is there an option or
> seeting for this? I havent seen one. Can anyone help me out on this.
> Thank you,
> Brett

http://mssql.meetholland.com/message/162162.aspx

Simon|||I doubt it will let you handle merging file text thru DTS. Anyone
correct me if I am wrong..!

DTS Package

Hello,Can i execute a DTS Package through a web form?I want when the user clicks on a link the DTS Package to be executed.Thank you in advance!

You can take a look atMicrosoft.SqlServer.Dts.Runtime Namespace

Google also gave me some examples:

http://www.sqldts.com/default.aspx?207

http://jaysonknight.com/blog/archive/2004/05/29/274.aspx

http://forums.aspfree.com/microsoft-sql-server-14/execute-dts-from-asp-net-19147.html

|||

that helps!

Thank you :)

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...