Dts works from SqlServer but not from ASP.net

Discussion in 'ASP .Net' started by luis valencia, Apr 19, 2005.

  1. 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
     
    luis valencia, Apr 19, 2005
    #1
    1. Advertisements

  2. luis valencia

    Guest 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!
    ***************************
     
    Guest, Apr 19, 2005
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.