DTS Package fail from ASP 1

[nightjam]

I have created a DTS package to
1. Execute a SQL task - which deletes all old data from a SQL database table

2. Import data from CSV file to SQL database Table.

Now the DTS package runs Ok and fine from enterprise manager but when I run it from ASP it fails at item/step 2.

ASP uses server authentication to access database and run DTS package which has owner/system administration privelliges and also access to the CSV files folder.

I also gave IUSER access to database and folders.

Any other troubleshootign help?

ASP code is as follows:
function runDTS(DeptName, DTSType)

Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1

Dim oPkg, oStep, sMessage, bStatus

Set oPkg = Server.CreateObject("DTS.Package")
'Load Employee Data
oPkg.LoadFromSQLServer "ServerName","sa","password",DTSSQLStgFlag_Default,"","","", "PackageName_"& DeptName & DTSType
oPkg.Execute()

bStatus = True

For Each oStep In oPkg.Steps
sMessage = sMessage & "<p> Step [" & oStep.Name & "] "
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
sMessage = sMessage & " failed<br>"
bStatus = False
Else
sMessage = sMessage & " succeeded<br>"
End If
sMessage = sMessage & "Task """ & oPkg.Tasks(oStep.TaskName).Description & """</p>"
Next

oPKG.UnInitialize
Set oStep = Nothing
Set oPKG = Nothing

'response.write("Status: " &bStatus)
'reponse.end()

if bStatus = False then
response.redirect "dtstest.asp?Results=UnkError&Description=" & sMessage
end if

end function

 

[Sheco]

it sounds like you are on the right track looking at the permissions... perhaps splitting these two tasks into two separate DTS packages will help with make debugging easier.

 

[nightjam]

I resolved problem. On IIS server I had to enable read permission for Scripting from web based application for the folder that contained the CSV files.

 

[Sheco]

Thanks for posting the follow-up so that others with the same issue may benefit from your solution.