Accessing webservices from DTS Activex Script

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

  1. bryan.galaz

    bryan.galaz 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!
     
    bryan.galaz, May 17, 2006
    #1
    1. Advertisements

  2. bryan.galaz

    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.
     
    Adam Short, Jan 12, 2007
    #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.