| Author |
 Topic Search  Topic Options
|
Gary 
Senior Member
Joined: 20 March 2002
Location: United Kingdom
Status: Offline
Points: 326
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Running DTS from ASP page
Posted: 05 November 2007 at 1:11pm |
Hi,
I am trying to run a DTS from an asp page and have had some success, but now I am struggling when it comes to data pump tasks that import data from an Excel file.
My environment is this:
Webserver
- asp page that uploads an Excel file (using ASPUpload)
- asp page then calls LoadFromSQLServer which executes a DTS located on the Database server
- running with Windows Authentication
Database Server (SQL 2000)
- DTS package that contains SQL tasks and a Data Pump task which loads data from the Excel file located on the Webserver
When I run the DTS from Ent Manager it works fine.
When I run from the asp page all SQL tasks work, but the Data Pump fails.
I have played around with the security of folder in which the Excel file is located, but to no avail.
When I built the code I did so in a single server environment (single machine acting as webserver and database server) and it ran just fine.
Now in a 2 server environment it fails which suggests maybe a permissions issue with accessing the data (Excel) file. But having toyed with the folder permissions I cant help but think that there is more tp it than that.
Any ideas?
Thanks
Gary
|
 |
socialanimal
Newbie
Joined: 14 October 2007
Location: Leeds, UK
Status: Offline
Points: 39
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 1:50pm |
Here's the code I use to do the exact same thing, the code will tell you where it's failing which is obviously the first thing you need to make sure of. It does sound like it is a permissions issue, but before going any further and speculating, it's probabaly easier to find out where its failing (at which step) and go on from there.
'***************************************************************************************'
' '
' Run the DTS Package '
' '
'***************************************************************************************'
const DTSSQLStgFlag_Default = 0
const DTSStepExecResult_Failure = 1
set objDTSPackage = CreateObject("DTS.Package")
blnSucceeded = true
objDTSPackage.LoadFromSQLServer "SQLServername", "username", "password", SQL_Server_TblName, "", "", "", "DTS_Package_Name"
objDTSPackage.Execute
for each objDTSStep in objDTSPackage.Steps
if objDTSStep.ExecutionResult = DTSStepExecResult_Failure then
strResult = strResult & "Package " & objDTSStep.Name & " failed.<br>"
blnSucceeded = false
else
strResult = strResult & "Package " & objDTSStep.Name & " succeeded.<br>"
end if
next
response.write strResult
|
|
|
Gary
Senior Member
Joined: 20 March 2002
Location: United Kingdom
Status: Offline
Points: 326
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 2:08pm |
Thanks for the suggestion....
I have used similar code to identify the step that is failing and it is indeed the Data Pemp step from the Excel file.
Furthermore, if I remove the Data Pump step, then the package completes successfully.
Conclusion is that there is definitely a permissions issue.
Problem remains that I cannot over it.
IIS is running under Windows Authetication only. Connection is using a DSN on the IIS server which points to the database on the remote database server.
Now I thought that with the above, access to the source Excel file would be using my user credentials. My user definitely has access to the file.
|
|
ctscott
Senior Member
Joined: 27 May 2003
Location: United States
Status: Offline
Points: 246
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 2:38pm |
|
thanks Socialanimal. i've always figured you could do it but didn't know how or what to search for.
|
|
|
|
socialanimal
Newbie
Joined: 14 October 2007
Location: Leeds, UK
Status: Offline
Points: 39
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 2:44pm |
 Gary wrote:
Thanks for the suggestion....
I have used similar code to identify the step that is failing and it is indeed the Data Pemp step from the Excel file.
Furthermore, if I remove the Data Pump step, then the package completes successfully.
Conclusion is that there is definitely a permissions issue.
Problem remains that I cannot over it.
IIS is running under Windows Authetication only. Connection is using a DSN on the IIS server which points to the database on the remote database server.
Now I thought that with the above, access to the source Excel file would be using my user credentials. My user definitely has access to the file. |
Just to bounce some questions off you so that we can try and get to the bottom of this then: On the SQL Server, who are you logged in as ? Yourself or a built - in account (eg administrator). On the excel file itself, do you have the inherited permissions for the entire folder ? And on the excel file you're only reading the information ? Have you tried using anyone elses authentication when executing the DTS ? If so, did that work ?
|
|
socialanimal
Newbie
Joined: 14 October 2007
Location: Leeds, UK
Status: Offline
Points: 39
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 2:45pm |
ctscott wrote:
thanks Socialanimal. i've always figured you could do it but didn't know how or what to search for. |
No sweat mate. Very handy tool. Don't really have a call to execute it in ASP so much, generally run it as a scheduled task overnight.
|
|
Gary
Senior Member
Joined: 20 March 2002
Location: United Kingdom
Status: Offline
Points: 326
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 2:52pm |
On the SQL Server, who are you logged in as ? Yourself or a built - in account (eg administrator). The server is logged on with a domain account that is a member of the local admins group on both the web and SQL server.
On the excel file itself, do you have the inherited permissions for the entire folder ? And on the excel file you're only reading the information
Yes, the Excel file has the inherited perms (everyone has Full Control access)
And on the excel file you're only reading the information ?
Yes, all I am doing is having the Excel file as a data source and using a Data Pump task to pull the data into SQL
Have you tried using anyone elses authentication when executing the DTS ? If so, did that work
Yes, I even tried using the domain account that I mentioned above (the one that is a member of local admin group) and it didnt work
Edited by Gary - 05 November 2007 at 2:53pm
|
|
Gary
Senior Member
Joined: 20 March 2002
Location: United Kingdom
Status: Offline
Points: 326
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 3:00pm |
I cant help but feel that when the DTS is invoked via an asp, it is not using any windows based authentication when trying to access the Excel file.
The code example provided above uses a SQL User Account to run the DTS - could that be part of the problem? If so, how do I run the same but using a domain account?
|
|
