Accessing webservices from DTS Activex Script

Discussion in 'ASP .Net Web Services' started by bryan.galaz@gmail.com, May 17, 2006.

  1. Guest

    I need to call a .NET webservice from a SQL Server 2000 DTS package
    Activex Script using VBScript. Does anyone know the syntax for this?

    Thanks!
     
    , May 17, 2006
    #1
    1. Advertising

  2. Adam Short Guest

    Not sure if you're still looking for a solution for this, but I came
    across your post while looking for something else and just happened to
    have the code handy.

    It's a bit long-winded, but it works. You have to build the SOAP request
    manually, but it's not too tricky, then you create an instance of the
    XMLHTTP object and use it to send the request. The code below is from a
    DTS ActiveX task I wrote a while back, the relevant data has been
    changed to protect the guilty.

    ' Build the soap request for the mail sender web method.
    dim SoapTest
    SoapTest=""
    SoapTest = SoapTest & "<?xml version=""1.0"" encoding=""utf-8""?>"
    SoapTest = SoapTest & "<soap:Envelope
    xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""
    xmlns:xsd=""http://www.w3.org/2001/XMLSchema""
    xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
    SoapTest = SoapTest & "<soap:Body>"
    SoapTest = SoapTest & "<SendMail xmlns=""http://myhost/MailService"">"
    SoapTest = SoapTest & "<SmtpServer>midas</SmtpServer>"
    SoapTest = SoapTest & "<To>" & DTSGlobalVariables("recipients").Value &
    "</To>"
    SoapTest = SoapTest & "<From>" & DTSGlobalVariables("sender").Value &
    "</From>"
    SoapTest = SoapTest & "<Subject>Your Subject Here</Subject>"
    SoapTest = SoapTest & "<Body>" & body & "</Body>"
    SoapTest = SoapTest & "</SendMail>"
    SoapTest = SoapTest & "</soap:Body>"
    SoapTest = SoapTest & "</soap:Envelope>"

    Set requestHTTP = CreateObject("Microsoft.XMLHTTP")
    requestHTTP.open "POST", SoapServer, false
    requestHTTP.setrequestheader "Content-Type", "text/xml"
    requestHTTP.setrequestheader "SOAPAction",
    "http://homer.ncha.net/MailService/SendMail" ' NOTE! READ this line
    carefully before editing it!!
    'It's the path to the .asmx file, with the filename replaced by
    the name of the method you want to call.
    requestHTTP.Send SoapTest

    if requestHTTP.Status = 200 then
    DTSGlobalVariables("success").Value=1
    DTSGlobalVariables("status").Value= requestHTTP.status
    DTSGlobalVariables("statusText").Value= requestHTTP.statusText
    DTSGlobalVariables("responseText").Value= requestHTTP.responseText
    Else
    DTSGlobalVariables("success").Value=0
    DTSGlobalVariables("status").Value= requestHTTP.status
    DTSGlobalVariables("statusText").Value= requestHTTP.statusText
    DTSGlobalVariables("responseText").Value= requestHTTP.responseText

    ' If the mail routine fails for any reason, write the output to a file
    so we can see what happened.
    set outfile=fs.createtextfile("C:\failedmail.html")
    outfile.write( requestHTTP.responseText)
    outfile.close
    End if

    That's pretty much it. The example is obviously a mail sender web
    method, but it'll work with any kind of web service. It's not the most
    elegant method in the world, but at least it works. I tried several
    different ways of doing it before I finally gave in and did it the long
    way.


    *** Sent via Developersdex http://www.developersdex.com ***
     
    Adam Short, Jan 12, 2007
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    0
    Views:
    928
  2. vml
    Replies:
    0
    Views:
    1,088
  3. Jay B
    Replies:
    1
    Views:
    138
    Jay B
    Oct 1, 2003
  4. Kerry Matt

    Problem accessing webservices

    Kerry Matt, Dec 16, 2003, in forum: ASP .Net Web Services
    Replies:
    9
    Views:
    147
    Dan Campbell
    Dec 18, 2003
  5. hellrazor

    webservices: simple question, accessing webservice members

    hellrazor, Nov 16, 2004, in forum: ASP .Net Web Services
    Replies:
    10
    Views:
    219
    Dan Rogers
    Nov 18, 2004
Loading...

Share This Page