Executing a DTS Package using an ASP (VBScript)

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • chris.dunigan@agwsha.nhs.uk

    Executing a DTS Package using an ASP (VBScript)

    I'm looking for an example of how to execute an existing DTS­ package
    from an
    ASP (VB)script and would appreciate any and all response. ­I don't
    even
    know if it's possible
    Thanks
    - Chuck Gatto


    Dan Guzman Apr 27 2000, 12:00 am show options

    Newsgroups: comp.databases. ms-sqlserver
    From: "Dan Guzman" <DGuz...@nospam please-earthlink.net&g ­t; - Find
    messages by this author
    Date: 2000/04/27
    Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
    Reply to Author | Forward | Print | Individual Message | Show original
    | Report Abuse

    This VBScript example loads and executes an existing DTS pac­kage from
    SQL
    Server.


    Option Explicit
    Const PackageName = "PackageNam e"
    Const ServerName = "ServerName "
    Const UserName = "UserName"
    Const Password = "Password"
    Dim DTSPk
    Set DTSPk = CreateObject("d ts.package")
    DTSPk.LoadFromS QLServer ServerName, UserName ,Password
    ,,,,,­PackageNa me
    DTSPk.Execute
    If DTSPk.Steps(1). ExecutionResult = 0 Then
    Response.Write "Package execution completed"
    Else
    Response.Write "Package execution failed"
    End If
    Set DTSPk = Nothing


    You can also create the entire package from scratch from wit­hin your
    asp and
    execute it.


    Hope this helps.



    Chuck Gatto <cga...@anchors ystems.com> wrote in message


    news:8eapo8$frj $1@slb7.atl.min dspring.net...


    - Hide quoted text -
    - Show quoted text -
    [color=blue]
    > I'm looking for an example of how to execute an existing D­TS[/color]
    package from
    an[color=blue]
    > ASP (VB)script and would appreciate any and all response.­ I don't[/color]
    even[color=blue]
    > know if it's possible
    > Thanks
    > - Chuck Gatto[/color]


    Chuck Gatto May 1 2000, 12:00 am show options

    Newsgroups: comp.databases. ms-sqlserver
    From: "Chuck Gatto" <cga...@anchors ystems.com> - Find messages by this
    author
    Date: 2000/05/01
    Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
    Reply to Author | Forward | Print | Individual Message | Show original
    | Report Abuse

    Below code works 100% in VB but the load fails in ASP. I g­et...
    "Microsoft OLE DB Provider for SQL Server. Login failed for ­user "\".
    error.
    I think the IIS (server a) is set for NT auth. and sql7 (on ­server b)
    as
    well but I can't be sure.
    Any idea what I should look for.
    Thanks



    "Dan Guzman" <DGuz...@nospam please-earthlink.net> wrote in m­essage


    news:sgi3hi617q o89@corp.supern ews.com...


    - Hide quoted text -
    - Show quoted text -
    [color=blue]
    > This VBScript example loads and executes an existing DTS p­ackage[/color]
    from SQL[color=blue]
    > Server.[/color]
    [color=blue]
    > Option Explicit
    > Const PackageName = "PackageNam e"
    > Const ServerName = "ServerName "
    > Const UserName = "UserName"
    > Const Password = "Password"
    > Dim DTSPk
    > Set DTSPk = CreateObject("d ts.package")
    > DTSPk.LoadFromS QLServer ServerName, UserName ,Password[/color]
    ,,,­,,PackageNa me[color=blue]
    > DTSPk.Execute
    > If DTSPk.Steps(1). ExecutionResult = 0 Then
    > Response.Write "Package execution completed"
    > Else
    > Response.Write "Package execution failed"
    > End If
    > Set DTSPk = Nothing[/color]

    [color=blue]
    > You can also create the entire package from scratch from w­ithin[/color]
    your asp
    and[color=blue]
    > execute it.[/color]

    [color=blue]
    > Hope this helps.[/color]

    [color=blue]
    > Chuck Gatto <cga...@anchors ystems.com> wrote in message
    > news:8eapo8$frj $1@slb7.atl.min dspring.net...[color=green]
    > > I'm looking for an example of how to execute an existing­ DTS[/color][/color]
    package
    from[color=blue]
    > an[color=green]
    > > ASP (VB)script and would appreciate any and all respons­e. I[/color][/color]
    don't even[color=blue][color=green]
    > > know if it's possible
    > > Thanks
    > > - Chuck Gatto[/color][/color]



    Dan Guzman May 1 2000, 12:00 am show options

    Newsgroups: comp.databases. ms-sqlserver
    From: "Dan Guzman" <DGuz...@nospam please-earthlink.net&g ­t; - Find
    messages by this author
    Date: 2000/05/01
    Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
    Reply to Author | Forward | Print | Individual Message | Show original
    | Report Abuse

    You can specify a trusted connection with flag 256 instead o­f
    username and
    password. For example:


    DTSPk.LoadFromS QLServer ServerName, , , 256,,,,PackageN a­me


    Assuming this is an intranet application running under NT 4.­0 and you
    want
    to execute the package under the invoking user's account, yo­u can do
    this as
    follows:


    Specify 'clear text' for the IIS Directory Security
    auth­entication
    Remove 'Everyone' from the access list on the files (req­uires
    NTFS) and
    grant permissions to the users
    Grant logins access to the database server


    With this method, users must enter their Domain\UserName and­ password
    when
    prompted.


    NT authentication presents a challenge when multiple servers­ are
    involved
    because NT 4.0 does not support delegation. See
    http://msdn.microsoft.com/workshop/s...e/security.asp for
    details.
    I understand Windows 2000 provides delegation capabilities b­ut this
    can be a
    bit tricky to implement.


    BTW, if your DTS package does not access SQL Server, you can­ save it
    to a
    file and use the LoadFromStorage File method instead.


    Hope this helps.


    If you need to use


    Chuck Gatto <cga...@anchors ystems.com> wrote in message


    news:8ekrkd$pmq $1@slb7.atl.min dspring.net...


    - Hide quoted text -
    - Show quoted text -
    [color=blue]
    > Below code works 100% in VB but the load fails in ASP. I­ get...
    > "Microsoft OLE DB Provider for SQL Server. Login failed fo­r user[/color]
    "\".
    error.[color=blue]
    > I think the IIS (server a) is set for NT auth. and sql7 (o­n server[/color]
    b) as[color=blue]
    > well but I can't be sure.
    > Any idea what I should look for.
    > Thanks[/color]


    Chuck Gatto May 6 2000, 12:00 am show options

    Newsgroups: comp.databases. ms-sqlserver
    From: "Chuck Gatto" <cga...@anchors ystems.com> - Find messages by this
    author
    Date: 2000/05/06
    Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
    Reply to Author | Forward | Print | Individual Message | Show original
    | Report Abuse

    Hey Dan
    Thanks again. I really appreciate your input and help.
    I actually solved the problem by calling dtsrun...
    I apologize for the delay getting back w/you but thee recent­ virus
    atack
    sidetracked me.
    Thanks again.


    "Dan Guzman" <DGuz...@nospam please-earthlink.net> wrote in m­essage


    news:sgsfh8spoo g87@corp.supern ews.com...


    - Hide quoted text -
    - Show quoted text -
    [color=blue]
    > You can specify a trusted connection with flag 256 instead­ of[/color]
    username and[color=blue]
    > password. For example:[/color]
    [color=blue]
    > DTSPk.LoadFromS QLServer ServerName, , , 256,,,,Package­ Name[/color]

    [color=blue]
    > Assuming this is an intranet application running under NT ­4.0 and[/color]
    you want[color=blue]
    > to execute the package under the invoking user's account, ­you can[/color]
    do this
    as[color=blue]
    > follows:[/color]

    [color=blue]
    > Specify 'clear text' for the IIS Directory Security[/color]
    au­thentication[color=blue]
    > Remove 'Everyone' from the access list on the files (r­equires[/color]
    NTFS)
    and[color=blue]
    > grant permissions to the users
    > Grant logins access to the database server[/color]

    [color=blue]
    > With this method, users must enter their Domain\UserName a­nd[/color]
    password when[color=blue]
    > prompted.[/color]

    [color=blue]
    > NT authentication presents a challenge when multiple serve­rs are[/color]
    involved[color=blue]
    > because NT 4.0 does not support delegation. See
    > http://msdn.microsoft.com/workshop/s...e/security.asp for[/color]
    details.[color=blue]
    > I understand Windows 2000 provides delegation capabilities­ but this[/color]
    can be
    a[color=blue]
    > bit tricky to implement.[/color]

    [color=blue]
    > BTW, if your DTS package does not access SQL Server, you c­an save[/color]
    it to a[color=blue]
    > file and use the LoadFromStorage File method instead.[/color]

    [color=blue]
    > Hope this helps.[/color]

    [color=blue]
    > If you need to use
    > Chuck Gatto <cga...@anchors ystems.com> wrote in message
    > news:8ekrkd$pmq $1@slb7.atl.min dspring.net...[color=green]
    > > Below code works 100% in VB but the load fails in ASP. ­ I get...[/color][/color]
    [color=blue][color=green]
    > > "Microsoft OLE DB Provider for SQL Server. Login failed ­for user[/color][/color]
    "\".[color=blue]
    > error.[color=green]
    > > I think the IIS (server a) is set for NT auth. and sql7 ­(on[/color][/color]
    server b) as[color=blue][color=green]
    > > well but I can't be sure.
    > > Any idea what I should look for.
    > > Thanks[/color][/color]



    chris.duni...@a gwsha.nhs.uk Jan 31, 10:05 am show options

    Newsgroups: comp.databases. ms-sqlserver
    From: chris.duni...@a gwsha.nhs.uk - Find messages by this author
    Date: 31 Jan 2005 10:05:00 -0800
    Subject: Re: Executing a DTS Package using an ASP (VBScript) Script
    Reply | Reply to Author | Forward | Print | Individual Message | Show
    original | Report Abuse

    Hi,


    I've tried adding this function to my ASP pages and get the ­following

    error message;


    Microsoft VBScript runtime error '800a01ad'


    ActiveX component can't create object: 'DTS.Package'


    /asp/pages/dts.asp, line 21


    Does anyone have any idea what I need to fix to get the DTS ­to work?


    I'm v. new to SQL Server and ASP so any help would be apprec­iated.
    Many thanks,
    Chris Dunigan

  • Andy O'Neill

    #2
    Re: Executing a DTS Package using an ASP (VBScript)

    <chris.dunigan@ agwsha.nhs.uk> wrote in message
    news:1107256044 .746188.14660@c 13g2000cwb.goog legroups.com...
    I'm looking for an example of how to execute an existing DTS­ package
    from an
    ASP (VB)script and would appreciate any and all response. ­I don't
    even
    know if it's possible
    Thanks
    - Chuck Gatto



    Your package.
    It works OK if you try and run it from Enterprise Manager on your machine?

    You tried a real user and password in those fields when you tried that
    method?
    This would be equal to the user you're logged on as?

    --
    Regards,
    Andy O'Neill


    Comment

    • Chris Dunigan

      #3
      Re: Executing a DTS Package using an ASP (VBScript)



      Hi Andy,

      Yes the DTS Package works fine when I run it from Enterprise Manager;
      and I have used the correct server name, username, password and
      packagename.

      Having done some more digging into this error I found a site that told
      me it could be something to so with the software installed on the PC on
      which the database resides.
      I think I might need to ensure that dtspkg.dll is correctly installed
      (along with a host of other dll and rll's).
      As I can't get hold of our IT guys today I haven't been able to see if
      this solves my problem.

      Does this sound like it may fix the problem??

      Regards,
      Chris

      *** Sent via Developersdex http://www.developersdex.com ***
      Don't just participate in USENET...get rewarded for it!

      Comment

      • Simon Hayes

        #4
        Re: Executing a DTS Package using an ASP (VBScript)


        "Chris Dunigan" <chris.dunigan@ agwsha.nhs.uk> wrote in message
        news:41ffb33f$1 _2@127.0.0.1...[color=blue]
        >
        >
        > Hi Andy,
        >
        > Yes the DTS Package works fine when I run it from Enterprise Manager;
        > and I have used the correct server name, username, password and
        > packagename.
        >
        > Having done some more digging into this error I found a site that told
        > me it could be something to so with the software installed on the PC on
        > which the database resides.
        > I think I might need to ensure that dtspkg.dll is correctly installed
        > (along with a host of other dll and rll's).
        > As I can't get hold of our IT guys today I haven't been able to see if
        > this solves my problem.
        >
        > Does this sound like it may fix the problem??
        >
        > Regards,
        > Chris
        >
        > *** Sent via Developersdex http://www.developersdex.com ***
        > Don't just participate in USENET...get rewarded for it![/color]

        These links might help:






        Simon


        Comment

        • Andy O'Neill

          #5
          Re: Executing a DTS Package using an ASP (VBScript)

          "Chris Dunigan" <chris.dunigan@ agwsha.nhs.uk> wrote in message
          news:41ffb33f$1 _2@127.0.0.1...[color=blue]
          > Hi Andy,
          >
          > Yes the DTS Package works fine when I run it from Enterprise Manager;
          > and I have used the correct server name, username, password and
          > packagename.
          >
          > Having done some more digging into this error I found a site that told
          > me it could be something to so with the software installed on the PC on
          > which the database resides.
          > I think I might need to ensure that dtspkg.dll is correctly installed
          > (along with a host of other dll and rll's).
          > As I can't get hold of our IT guys today I haven't been able to see if
          > this solves my problem.
          >
          > Does this sound like it may fix the problem??[/color]

          If no dts package works when run from a job on the server.


          Quite frankly, I'm confused by your huge initial post as to which technique
          you're trying and what's going on.


          I'm wondering if this is running and owned by a user can run the dts package
          OK on the server using whichever of the alternatives you posted you're
          currently trying.

          Try this with the package if it's vbscript using any activex.
          Open DTS Designer, right-click the task, select Workflow, then Workflow

          Properties. On the Options tab, check "Execute on main package thread"


          --
          Regards,
          Andy O'Neill


          Comment

          Working...