Create complex-type variables for a Web Service Task

Question

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?

Answer 1

Okay so I came across the same problem. I needed to pass one parameter as complex type.

1. Create a Web Service task in your package.
2. Fill all the needed properties at General tab: HttpConnection and WSDFile
3. Fill properties in Input tab: Service, Method
4. Below click on Value, manually enter the value you need (mine is 2021-11-15)
5. Deploy and execute package to be sure everything is OK

After this easy steps go into folder where package is localated. Right click on package file (Package.dtsx) and select Open with > Notepad. With find function in notepad search the value you manually inserted. The part which we are looking for looks in my case like this

                  <WSTask:ComplexValue>
                    <WSTask:ComplexProperty
                      WSTask:Name="date"
                      WSTask:Datatype="dateTime"
                      WSTask:ParamType="Primitive">
                      <WSTask:PrimitiveValue>2021-11-15</WSTask:PrimitiveValue>
                    </WSTask:ComplexProperty>
                  </WSTask:ComplexValue>

Finally I found what I was looking for. Now for the second part I needed to be that parameter changing by current date when I execute that package. In powershell I managed to write a code that change date part in string: <WSTask:PrimitiveValue>2021-11-15</WSTask:PrimitiveValue> to current date, everytime when the package is executed. The code looks like this:

$Now = Get-Date -Format "yyyy-MM-dd"    
$Yesterday = (Get-Date).AddDays(-1).ToString("yyyy-MM-dd")
$file = ((Get-Content -path "C:\Package.dtsx" -Raw) -replace "<WSTask:PrimitiveValue>$Yesterday</WSTask:PrimitiveValue>", "<WSTask:PrimitiveValue>$Now</WSTask:PrimitiveValue>")
[System.IO.File]::WriteAllText("C:\Package.dtsx",$file)
# This part will execute the package #
dtexec.exe /f "C:\Package.dtsx"

After all this, I planned this script in Task Scheduler and it works. In my case changing the type of request from complex to simple wasn't an option and all I needed was just one parameter to pass. Hopes it gonna help somebody.

Answer 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.

Answer 3

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.