Dts works from SqlServer but not from ASP.net

[luis valencia]

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


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

 

[Guest]

Are you logged in when you are in the ASP.NET application or are you under
the user context IUSR_MachineName? In other words, does your web app force a
windows login? If not, you are anonymous and that is why.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************