Well that's a mouthful for a title. Let me start out by saying that I am not a programming per se but a DBA. I can write some MSSQL query with the best of them but my knowledge of C# is pretty limited. I have a piece of software that I need to communicate with via web services to push some data to all within the constraints of SQL Server Integration Services. I can read from the DB directly but I can not write to it. I'm querying some data, emailing that data, and I need to write back to the software for reach record we emailed through web services. The WSDL is available but like I said, zero knowledge of C#. I was able to build a SOAP envelope through SOAPUI that works perfectly and I would like to figure out a way call SOAP using a C# script in my SSIS package.
I have also tried to run this in the Web Service Task in SSIS and my WSDL file only gives me 4 methods when I should have close to a hundred. It would be easier there to sort out but I understand there are some limitations with the SSIS Web Service Task.
I have seen this thread: Client to send SOAP request and received response
Which unfortunately does not work for me when I copy and paste my SOAP and header into the code. To throw another wrench this thing, this is a protected web services which requires 3 credentials to have a token issued for passing data back and forth. A side question is can I run multiple SOAP calls (first to authenticate, then to push my data into the web service) through a C# script?
Here was my second soap call after getting the token that I was able to run:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"xmlns:v2="http://www.WSAPI.AMS360.com/v2.0">
<soapenv:Header>
<v2:WSAPIAuthToken>
<v2:Token>tokenremoved</v2:Token>
</v2:WSAPIAuthToken>
</soapenv:Header>
<soapenv:Body>
<v2:InsertActivity_Request>
<!--Optional:-->
<v2:Activity>
<v2:AssignedTo>93725c05-f77b-49a1-9edd-6ce9fd5505c1</v2:AssignedTo>
<!--Optional:-->
<v2:ActivityType>customer</v2:ActivityType>
<v2:ActivityAction>% of ownership</v2:ActivityAction>
<!--Optional:-->
<v2:ActivityDate>11/04/2014</v2:ActivityDate>
<!--Optional:-->
<v2:ActivityTime>11:54</v2:ActivityTime>
<!--Optional:-->
<v2:EmployeeCode>!$5</v2:EmployeeCode>
<v2:Description>web services test code successfully inserted</v2:Description>
</v2:Activity>
</v2:InsertActivity_Request>
</soapenv:Body>
</soapenv:Envelope>