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 overviewof 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 thequery of the first databaseis the input in the signature ofa 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 StringEnd
ClassIn the input pane for the Web Service,when Iclick in the value of the twoGTTimestamps like I'm going to hardcode them in,another windowpops 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 withtheWeb Service for a few minutes and giving up on that, I decided tocreate a script task that is supposed to create two GTTimestamp objects and assign them totwo object variables in the package for passing to the WebService Task. The first challenge was to get the web service toplay nicewith 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 totry 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.