Dts works from SqlServer but not from ASP.net

I have an asp.net page that executes a DTS. When I execute that DTS from enterprise manager it takes about 5000 rows from the as400 and insert into sql server
It works right. but when I execute it from my asp.net page I have this error.
Error al procesar DTS TransferirDatos(ExistMP) en el paso DTSStep_DTSActiveScriptTask_1System.Exception: Error al procesar DTS TransferirDatos(ExistMP) en el paso DTSStep_DTSActiveScriptTask_1 at LibreraLentos.exec.ejecuta_SP_EXISTENCIASMP() in C:\Documents and Settings\luisvalen\Mis documentos\Visual Studio Projects\InventariosLentos\LibreraLentos\exec.vb:line 43 at InventariosLentos.generacionprocesomateriaprima.btnenviar_Click(Object sender, EventArgs e) in C:\AplicacionesWeb\InventariosLentos\generacionprocesomateriaprima.aspx.vb:line 60LibreraLentos

I have this on my ASP page
Private Sub btnenviar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnenviar.Click
Try
objexecsp.ejecuta_SP_EXISTENCIASMP()
lblmensajes.Text = "Proceso generado satisfactoriamente"
Catch ex As Exception
lblmensajes.Text = ex.Message + ex.GetBaseException.ToString + ex.Source.ToString
End Try
End Sub
this on my Data Classs
Public Function ejecuta_SP_EXISTENCIASMP()
' call UpdatePrice using a parameter array of SqlParameter objects
Try
Dim ejecutardts As New cDTS
ejecutardts.EjecutarDTS("TransferirDatos(ExistMP)")
Catch ex As Exception
Throw ex
End Try
End Function
This is what executes the DTS
Imports System.Runtime.InteropServices
Imports System.Configuration.ConfigurationSettings
Imports DTS
Public Class cDTS
Public Sub EjecutarDTS(ByVal NombreDTS As String)
Dim pkg As New DTS.Package
Dim oStep As DTS.Step
Try
pkg = New DTS.Package
'pkg.LoadFromSQLServer(AppSettings("MED20NT"), AppSettings("user"), AppSettings("pwd"), DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "pruebaCdr1")
pkg.LoadFromSQLServer("MED20NT", "sa", "prueva", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", NombreDTS, "")
pkg.AutoCommitTransaction = True
pkg.Execute()
For Each oStep In pkg.Steps
If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then
Throw New Exception("Error al procesar DTS " & pkg.Name & " en el paso " & oStep.Name)
End If
Next
Catch ex As System.Runtime.InteropServices.COMException
Throw ex
Catch ex As Exception
Throw ex
Finally
pkg.UnInitialize()
pkg = Nothing
End Try
End Sub
End Class

This is the CODE of my dts
but as I told before it Works when I right Click on it in enterprise manager
Dim ConnSql
Dim ConnDb2
'* Función para Conexion a Base de Datos ASW en Med13nt
Function ConexionSql()
'On Error Resume Next
Dim strConexion
strConexion = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=True;" & _
"User ID=sa;Password=xx;" & _
"Initial Catalog=asw;" & _
"Data Source=Med20nt"
Set ConnSql = CreateObject("ADODB.Connection")
ConnSql.Open strConexion
If Err.Number <> 0 then ConexionSql = False
End Function
'* Función de Conexion a ASW as400
Function ConexionDb2()
'On Error Resume Next
Dim strConexion
strConexion = "DSN=asw1;User ID=CLAUDIMON;Password=xx"
Set ConnDb2 = CreateObject("ADODB.Connection")
ConnDb2.Open strConexion
If Err.Number <> 0 Then ConexionDb2 = False

' conexion por OLEDB
' Dim strconexion
' strconexion = "Provider=IBMDA400.DataSource.1; Data source=10.2.0.10;User Id=FVENTAS;Password=xx"
' Set ConnDb2 = CreateObject("ADODB.Connection")
' ConnDb2.Open strConexion
' If Err.Number <> 0 Then ConexionDb2 = False
End Function

'* Trasfiere los datos de ASW a Med20nt
Private Function Transferir()
'On Error Resume Next
Dim strSql
Dim producto
Dim strSql2
Dim strSql3
Dim strCero
Dim strcomilla
Dim RS
Dim RS2
Dim RS3
Dim Contador
Dim StrBodega
strBodega=" "
contador=1
strcomilla="'"
strCero="0"
Set RS = CreateObject("ADODB.Recordset")
Set RS2 = CreateObject("ADODB.Recordset")
Set RS3 = CreateObject("ADODB.Recordset")
strSql3 = "SELECT distinct producto from sumvenmpf"
ConnSql.Execute (strSql3)
RS.Open strSql3, ConnSql
' ConnSql.Execute (strSql3)

Do Until RS.EOF
Set RS2 = CreateObject("ADODB.Recordset")
strSql = "SELECT srprdc, srsrom, srplan,ctname,sum(srsthq), sum(srpurq), sum(srcusq), sum(srpicq) FROM HCB453AFIH.srbsro,HCB453AFIH.srbctlsd where ctsign=srplan and srprdc="&strcomilla&RS.Fields("producto").value&strcomilla &" and (srsthq>0 or srpurq>0 or srcusq>0 or srpicq>0) group by srprdc,srsrom,srplan,ctname"
contador=0
RS2.Open strSql, ConnDb2
contador=contador+1
Do until RS2.EOF
strSql2 = "INSERT INTO ASW.dbo.Existenciasmp (srprdc,srsrom,srplan,ctname,srsthq, srpurq, srcusq, srpicq ) values ('" & RS2.Fields("srprdc").value & "','" & RS2.Fields("srsrom").value & "','" & RS2.Fields("srplan").value & "','" & RS2.Fields("ctname").value & "','" & RS2.Fields(4).value & "','" & RS2.Fields(5).value & "','" & RS2.Fields(6).value & "','" & RS2.Fields(7).value & "')"
ConnSql.Execute (strSql2)
RS2.MoveNext
Loop
RS2.Close
Set RS2 = Nothing


RS.MoveNext
Loop
RS.Close
Set RS = Nothing
End Function
'* Inicio de la Interfaz
Function Principal()
'On Error Resume Next
Call ConexionSql
Call ConexionDb2
Call Transferir()
Principal = DTSTaskExecResult_Success
End Function

The strange thing here is that it always returns 864 rows.
when executing from asp.net and 5000 from enterprise manager

I have the same issue, which I set up a DTS package in the SQL Server. When I execute package from the Enterpise manager, it ran with no error.

However, when I run it from an asp.net page, it fails. I use the below code to validate the error of the steps. What could it be wrong?

Thanks!

Terrence

DimWithEvents oPkgAs DTS.Package

oPkg =New DTS.Package()

Dim vStepAs DTS.Step

ForEach vStepIn oPkg.Steps

'test if the activescripttask type of task is in this step, and dont have to the transaction if so

'use instr (return value of the position of string location)

IfNot InStr(1,CType(vStep.Name,String), "DTSActiveScriptTask", CompareMethod.Text) > 0Then

IfNot InStr(1,CType(vStep.Description,String), "Update HTMLText Table", CompareMethod.Text) > 0Then

vStep.JoinTransactionIfPresent =True

Else

vStep.JoinTransactionIfPresent =False'if the step is the 'Update HTMLText Table' step then dont join to transaction

EndIf

Else

vStep.JoinTransactionIfPresent =False'if the step is executing a script task, then dont join the transaction

EndIf

vStep.RollbackFailure =True

Next

'modify transaction properties of the package

oPkg.UseTransaction =True

oPkg.AutoCommitTransaction =True

'set the current time to later evaluate the execution time

sTimestart = Now()

'execute the package

OnErrorResumeNext

oPkg.Execute()

OnErrorGoTo 0

'evaluate total execution time of the package

Dim sTime

sTime = DateDiff("s", sTimestart, Now())

'error logging

'default the flag to true (successful execution) and set to false if error occurs

Dim flagAsString =True

Dim sErrAsString

Dim strLogAsString

Dim oDTSStepAs DTS.Step

ForEach oDTSStepIn oPkg.Steps

If oDTSStep.ExecutionResult = oDTSStep.ExecutionResult.DTSStepExecResult_FailureThen

.....

|||

DTS is SQL Server Agent dependent, your SQL Server Agent needs a service account. Try the links below. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp