February 4, 2015 at 10:26 am
Hi all,
I'm trying to run a web service task from within SSIS (2008 R2). If I key the input parameters directly into the input form, it works a treat, but I need to be able to set one of the parameters dynamically, and it's a string array.
This article https://social.msdn.microsoft.com/Forums/sqlserver/en-US/50f6f5b0-00ff-4e5b-8038-6073a01ac4ab/arrays-in-webservice-ssis-2008 looked promising, but I ran into the same problem as mentioned at the bottom of it, which wasn't answered.
In a nutshell, when I initially go into the input form in the task designer, it shows the parameter (correctly) as string[]. If I change it to use an Object variable, then on going back into the designer, it's been changed to string, and the web service call fails (even when the variable is built up as a string array in a previous script task).
Anyone have any ideas (other than just building a proxy and calling the ws from a script task, which I'm going to try)?
Thanks
January 5, 2016 at 4:10 am
I didn't manage to get this to work using the "proper" WS task. Did get it working from a script task though, which worked a treat. I generated a proxy class from the wsdl using wsdl.exe (see here https://msdn.microsoft.com/en-GB/library/ms155134%28v=sql.105%29.aspx if you're not familiar), pasted the proxy code into a script task and called it from there (the string array being passed in in a variable).
#region WSDL generated web proxy code
public partial class theWebService : System.Web.Services.Protocols.SoapHttpClientProtocol
{
/// Loads more generated code...
public string[] GetResult([System.Xml.Serialization.XmlArrayAttribute(IsNullable = true)] [System.Xml.Serialization.XmlArrayItemAttribute(Namespace = "http://schemas.microsoft.com/2003/10/Serialization/Arrays")] string[] input)
{
object[] results = this.Invoke("GetResult", new object[] {
input});
return ((string[])(results[0]));
}
/// Loads more generated code...
}
#endregion WSDL generated web proxy code
public void Main()
{
string[] input = (string[])(Dts.Variables["WSInputs"].Value);
string[] result;
string theURL = Dts.Variables["WebServiceURL"].Value.ToString();
theWebService ws = new theWebService();
ws.Url = theURL;
try
{
result = ws.GetResult(input);
Dts.Variables["Results"].Value = result;
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Events.FireError(-1, "Get Rate", "Error from Service - " + ex.Message, "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
I think I may have had to modify the code generated by wsdl, but can't remember the details (the project requirements changed, and I didn't need this call after all, so didn't take it any further or document it).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply