Create complex-type variables for a Web Service Ta

问题:

In SSIS, I already have a Web Service Task using a WSDL for sending SMS. I am indeed able to send SMS using this task.

I want supply values to this task from the database, such as Mobile Number, Message body, User ID, etc.

How can I create a complex type user variable that can be passed as input to a Web Service task?

回答1:

It looks like the only answer is to change the web service to accept only simple types as parameters. I have scoured the web and there seems to be no way to dynamically create complex types for consumption by the input values in the web service task.

回答2:

The more 'easy' way is to use the script component for bypassing variables to a web service. Check http://amolpandey.com/2016/09/26/ssis-script-task-to-obtain-geo-cordinates-from-address-text-via-google-api/ & http://www.sqlmusings.com/2011/03/25/geocode-locations-using-google-maps-v3-api-and-ssis/. Tested and working. Using this task you can bypass the SSIS variables/parameters. Example: Getting ID, addreess, zipcode, city, country from a table with an execute SQL Task. Change Resultset:Full result set on General tab. Then on resultset tab add Result_Name:0 & Variable_Name: User::YourObject. Then the next task will be a Forlooptask editor (Foreach ADO Enumerator ,Collection tab - Ado object source variable: User::YourObject, enumeration mode: rows in the first table, variable Mapping tab - Variable User::Id, 0 | address,1 etc.). Inside the Forlooptask editor you add a data flow task, which the source of this task will be a script component. If you be more specific about your logic,we may assist you more.