Help Passing Complex Variable Types to a Web Service Task

Question

I need a little help here and appreciate any insight into this issue.

I am building an SSIS package that retrieves data from a database to use in a web service task. So let me give you a little more broad overview of the package so you can understand how this is supposed to roll. A database is queried and those values are dumped into a recordset. A foreach loop uses each row of variables to call the web service and dump the returned values to another database. The first database holds a bunch of fields, but the four fields of interest are: a StartDate (DateTime), a StartFormat (single char), an EndDate (DateTime) and an EndFormat (single char). The output from the query of the first database is the input in the signature of a web service's .Load method. Sounds easy, right?  Sure, why not?

Well I dragged the Web Service Task on to the pane and took a look inside. Lo and behold, I can hardcode the variables in for the web service or I can assign the inputs to package variables. How fancy, thanks Microsoft!

But that's where the difficulty begins. The method call for the web service looks like this:  service.Load(string, int, GTTimestamp1, GTTimestamp2). The web service is expecting a string, an int, and two objects of the type GTTimestamp, which is a very simple class defined as this:

<System.Xml.Serialization.SoapTypeAttribute("GTTimestamp", "http://util.gtdw.pci.com")> _

Public Class GTTimestamp

Public calendar As Date

Public displayFormat As String

End Class

In the input pane for the Web Service, when I click in the value of the two GTTimestamps like I'm going to hardcode them in, another window pops up saying "Enter the values for complex type -in4" and the pane looks exactly like the previous pane with one very important exception: There is not place to check to assign the value from a package variable!!! Dang you Microsoft!!! I don't really understand why they would leave us out to dry on this... Oh, well, maybe they'll take care of it later...Time to work around it.

SSIS does allow you to create a variable of type System.Object, so after playing with the Web Service for a few minutes and giving up on that, I decided to create a script task that is supposed to create two GTTimestamp objects and assign them to two object variables in the package for passing to the WebService Task. The first challenge was to get the web service to play nice with the script. For those who have never done this, use a command prompt and the wsdl.exe to generate a .vb or .cs file to add to your script file using the right click, add existing item...

Once the file was accessable to my scripts, I created two GTTimestamp objects and assigned them to the Package variables of type System.Object. Running the package, I got this error:

"Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Type 'ScriptTask_8c868490237b4220b582bdc7c7a3ecae.GTTimestamp' in assembly 'VBAssembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' is not marked as serializable.

Not marked as serializable, huh. Okay, so I made the GTTimestamp serializable by adding the <Serializable()> before the class declaration. Then the error changed to:

The error is: Type is not resolved for member 'ScriptTask_8c868490237b4220b582bdc7c7a3ecae.GTTimestamp,VBAssembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'.

And here I am, at a loss for what to do from this point. I'm a little lost, so I thought I'd stop and ask for directions. I'm sure I'm not the first to want to use complex variable types.

Only two options at this point. One is to try and store the GTTimestamp in the database and see if SSIS can deal with that. I'm not sure how to store objects in a database or if that is even an option for me, but it came to mind so it made it into this post. The other is to get this to work through the script above that I have run into a wall.

Again, any help is appreciated. Thanks for your time.

Answer 1

in.the.dark,

As you've already generated the WSDL, and created the types in the Script Task, why not just call the Web Service from there as well? 

Donald Farmer's book Scripting SQL Server 2005 Integration Services has an entire chapter devoted to interacting with Web Services from the Script Task.

HTH,
Patrik

Answer 2

Thanks Patrik. Actually, Donald Farmer's book actually yeilded how to generate the WSDL. I could do this through the script and may just have to. The idea though is to use SSIS's Web Service Task the way it was intended, which we may be learning that it is not complete or really useful beyond passing a simple data type. Coming to some sort of conclusion as to why it doesn't work intuitively and how to work around it is what these forums are for.

There is a lot that SSIS doesn't do quite right. For instance, when my XML data is returned from the web service, the web service's output is then assigned to a variable of type string. Why string? Because when you go from a Web Service Task to a Data Flow, and in the data flow, pull in an XML Source and set it to 'XML from Variable' it will only allow you to select a string typed variable. No big deal right? But the XML doesn't load right either. If you have an XML file like this:

<?xml version="1.0"?>
<Note xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <From>Me</From>
  <DateCreated>
    <Date>2007-02-13</Date>
    <Time>12:00AM</Time>
 </createdOn>
 <To>You</To>
 <Body>This is the Message</Body>
 </Note>

You will not be able to expose the non-complexType variables as defined by the XSD. Meaning that the XML Source would not see the data from the From, To and Body fields. I've quadruple checked the XSD and hand written several. I found that the only way I could get it to work is by making the XML nodes I want data from under a root that is a complexType. I have a script that takes off the XML's header that is returned by the web service and changes it so the XML looks like this:

<?xml version="1.0"?>
<root1>
<Note>
  <From>Me</From>
  <DateCreated>
    <Date>2007-02-13</Date>
    <Time>12:00AM</Time>
 </createdOn>
 <To>You</To>
 <Body>This is the Message</Body>
 </Note>
</root1>

I think that this approach is barbaric at best. The intuitive way that this would work is that the XML Source would be able to handle the XML returned. I understand what the XML Source is trying to do, expecting the root to contain nothing and treating the next level of nodes as rows in a dataset, if you will.

My point is that SSIS doesn't work the way you would think it would. The idea is to simplify the data flow process. If I wanted to write a script that would do it all, I would and it would run fine. SSIS is supposed to be a tool for doing the same thing, but with less effort. But as with any good tool, there is a learning curve. I just would hate to learn that SSIS isn't worth my time, and that the only thing that I'll end up doing is writing a long script task and just have SSIS handle the running of the script. Sorry for the rant, I'm running into too many, "it doesn't work like it should" scenarios.

Any other suggestions? I'm still at a loss.

Answer 3

A month has gone by, and I've been watching for a response on this.  Has anyone found how to make this work yet, or are we to all rewrite our web services and/or just script everything outside of SSIS altogether?

 

I've got exactly the same task to accomplish and I'm waiting breathlessly for an answer to this.  Nothing I've tried has worked yet.

 

Thanks for any help,

Paul

 

Answer 4

Paul,

Where you looking for an answer to the complex variable issue or the xml not parsing correctly issue?

Answer 5

I'd be interested in hearing more about the complex variable solution.  I've got a similar problem, where the web service I'm trying to interact with is expecting a parameter of some custom/complex type.  In a regular C# project, I know how to create a variable of that type, but not in SSIS. 

I agree that you should be able to select 'variable' for each of the parameters on the custom type screen.  Any suggestions

Trey

Answer 6

Seeing as I'm not the only one who ended up finding this page while searching, it would be very helpful if someone could briefly explain how to create/assign a complex variable in a c# SSIS script task.