Calling a .NET WS from DTS

[exactscience]

OK, here's what seems to be the deal. A .NET Web Service that the sql server 2000 DTS package will consume must return a string or other simple data type that is not serialized.
I had the creator of the WS overload the method so it returned the XML as a string instead of a DataSet. You will need to have the MS Soap tlb 3.0 loaded.
The code below works on my local dev SQL server calling an outside WS. I later had to parse out the XML to find exactly what I was looking for. This XML can be loaded into a DOM doc or you can just parse out with string functions. Let me know if this helps.


'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()

dim sResponse, sCounties


'Call Functoin that calls web service

sResponse = Call_NET_WS("

http://blah.com/blah_WS/blah_WS.asmx?WSDL",

DTSSource("State"))

sCounties = GetCounties(sResponse)

DTSDestination("Counties") = sCounties

Main = DTSTransformStat_OK

End Function

Function Call_NET_WS(URL, sState)

Dim objSoapClient, strResponse

Set objSoapClient = CreateObject("MSSOAP.SoapClient30")

objSoapClient.MSSoapInit Url

strResponse = objSoapClient.ReturnCountiesXML(sState)

Call_NET_WS = strResponse

End Function

Function GetCounties(sInput)

Dim sOutput, stemp

pos = instr(1, sInput, "<CountyName>")
do while pos > 0
sOutput = sOutput & Scrape_Value(sInput, "<CountyName>", "</CountyName>") & ", "
pos = instr(pos + 12, sInput, "<CountyName>")
loop

'trim off last comma and space

sOutput = left(sOutput, len(sOutput) - 2)

GetCounties = sOutput

End Function

Private Function Scrape_Value(sInput, sStart, sEnd)
Dim pos1, pos2, sVal

pos1 = 0
pos2 = 0
sVal = ""

pos1 = InStr(1, sInput, sStart)

If pos1 > 0 Then

pos2 = InStr(pos1 + Len(sStart), sInput, sEnd)

sVal = Mid(sInput, pos1 + Len(sStart), pos2 - pos1 - Len(sStart))

sVal = Trim(sVal)

'Cut Input String
sInput = Mid(sInput, pos2 + Len(sEnd))

End If

Scrape_Value = sVal

End Function