SQL Server DTS permissions problem

From: DC (cyrdg@hotmail.com)
Date: 10/16/02 

From: cyrdg@hotmail.com (DC)
Date: 16 Oct 2002 07:31:04 -0700

All, I am trying to execute a SQL 2000 DTS package from within an
.aspx page (See code snippet below). The package works fine if I run
it through enterprise manager. However, when I execute it via the
below listed code the package fails. The package is actually created,
the global variable is set and the package is executed. The problem
seems to be in the area of permissions. The first task in the package
attempts to execute a simple activex script which creates a
filesystemobject and looks for a .txt file on our network in a
particular directory. Even though the dir and file both exist, the
error in the DTS generated log file says that no such dir exists. I've
tried to duplicate the problem in enterprise manager by not using
windows auth and by instead entering the username and password
(different from my network logon and pw) in the code below but to no
avail. Any help would be greatly appreciated.

DC

    Public Function RunDTS() As String

        Dim oPackage As New DTS.Package()
        Dim iIndex As Integer
        Dim sReturnVal As String
        Dim iStatus As Boolean = True

        If ImpersonateValidUser("domainusername", "domain",
"domainpassword") Then
            oPackage.LoadFromSQLServer("ServerName",
"SqlSrvrusername", "SqlSrvrpassword",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, Nothing, Nothing,
Nothing, "DTS Package Name", "")
            oPackage.GlobalVariables.Item("gvIndex").Value = 1 'gv
used in execution of package
            oPackage.Execute()
            For iIndex = 1 To oPackage.Steps.Count
                If oPackage.Steps.Item(iIndex ).ExecutionResult =
DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
                    oPackage.Steps.Item(iIndex).GetExecutionErrorInfo(lpErrorCode)
                    iStatus = False
                    sReturnVal = oPackage.Steps.Item(iIndex).Name & "
in the " & oPackage.Description & " failed.<BR>"
                End If
            Next
            If iStatus = True Then
                sReturnVal = oPackage.Description & " Successful<BR>"
            End If
        End If

        Return sReturnVal

    End Function

Relevant Pages

  • Cross post recommended on this seemingly sinple problem
    ... >I have an ActiveX Script task in a DTS package which executes fine ... Why does the scheduled job not execute ... > schedule and the account is a domain account with windows ... even with both the package and the job owned by the same account (I ...
    (microsoft.public.sqlserver.dts)
  • Re: Yet Another Execution was Canceled by User Problem SOLVED
    ... I've found that if I attempt to execute an execute dts package task ... > disconnected edit window). ...
    (microsoft.public.sqlserver.dts)
  • Nested packages not firing events when executed via .NET interop
    ... This works fine on individual packages, but when I execute a package ... void PackageEvents.OnError(string EventSource, int ErrorCode, ...
    (microsoft.public.sqlserver.dts)
  • Re: Executing DTS from Code
    ... Now go to QA and execute the proc. ... Everything works fine on my development workstation which has SQL Server ... I moved the DTS package to the production server and ... runs the DTS package. ...
    (microsoft.public.sqlserver.dts)
  • Re: Re: dtsrun.exe hangs before complete
    ... > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... >> I created a simple DTS package to export data of some tables ... Anyway trying some options on DTS package I found that if I select ... Now I want to execute the package by DTS client on which no SP is yet ...
    (microsoft.public.sqlserver.dts)