1

In my project, I have simply created the web service for addition of number and returning the result in variable from web service task via SSIS as explained in following screen,

enter image description here

when I am using file connection mode then I am getting xml file saved on my hard drive in the following format,

<?xml version="1.0" encoding="utf-16"?>

35

I have did following attempt to insert that result that is 35 in my database table either by file connection or through variable

  1. I have created Execute SQL task to perform query by procedure, follow the link enter link description here

  2. And also I tried to run Execute SQL task by accessing variable, follow the link [Inserting Data into SQL Server from variables via SSIS]

and now after this all trial, my concern is am I able to successfully store result in user variable from web service as shown in first screen shot since I am getting the empty value of variable as refer in link 2,

but when I am storing data on hard drive then it is successfully storing the result in xml format and if I am incorrect, then please provide the correct procedure or helping links...

My main motto is to run a web service for addition of two numbers and get the result in user defined variable through web service task via SSIS and then insert that acquired data into sql database.

And also, what this double green arrow means in my following screen shot from web service task enter image description here

Community
  • 1
  • 1
Reshma
  • 864
  • 5
  • 20
  • 38

1 Answers1

0

and now after this all trial, my concern is am I able to successfully store result in user variable from web service as shown in first screen shot since I am getting the empty value of variable as refer in link 2,

Your variable will only ever be populated at runtime (while the package is running). It will never be populated at design time (when you are pressing the evaluate button) unless you type something manual into it.

The second green arrow is just another data flow. If the XML task has more than one output, you use this second arrow to join it to something else. It's just a handle for you to grab.

This is well outside the scope of your original question, but given that you have indicated you want to save the entire XML file history into your to the database, you need to first focus on that. It appears from your other question that you acheived this, now you need to use SQL functions to shred that out. Can you verify that you have managed to save the XML file into the database (in an XML data type)?

If so please close this question and focus on shredding it out of the database. Here is a link to an example of working with XML datatypes. You need to work out how to extract your 35 using the methods mentioned in this link.

Working with the SQL Server XML data type

Here is an example of working with the XML data

Community
  • 1
  • 1
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • @ElectricLlama.. I mean to say at runtime is the variable is populating or not, but my code finally executed when I placed constant value in query, that time value is inserted into database, so I manipulated the data type of column to take the xml content and whole of the content of the xml file is entering into database, now I am suppose to take only added result that is any numerical value, I tried for xml task in between web service task and execute sql task but the thing is in xml task, I am not able to process through variable holding the whole of xml string returned by web service task – Reshma Sep 11 '13 at 09:48
  • In your example, the trivial solution would be to use a string function to remove the `` part, leaving just the number. Then you can cast that to an integer and work with it further. The _proper_ solution is to _shred_ the XML using XPath, but this is tricky inside SSIS. Is the objective of this to learn about SSIS or is there a real business problem? – Nick.Mc Sep 11 '13 at 22:50
  • @ElectricLlama.. This is a real business problem, I have to consume some web service and update the tables from the result return from web service,thus mentioned in above question is simple web service created by me for addition of numbers and I am trying to insert that result received into database, I have succeeded in updating the table through Execute SQL Task but the problem is I am unable to insert only numerical value, whole of the xml sting is getting inserted into my table. – Reshma Sep 12 '13 at 05:38
  • Is that what your XML will always look like? because the trivial solution is to simply remove the bits of the string you don't like. But if you want to use XML properly, all the solutions I have come accross involve inserting the XML into the database then using database XML functions to shred out what you need. – Nick.Mc Sep 12 '13 at 07:23
  • @ElectricLlama...can you please guide me by providing clarification or helping matter or links on using database XML functions to shred it for getting my required value in database again. – Reshma Sep 12 '13 at 08:17
  • First I have some questions: 1. Do you have a series of numbers coming through over time? Do you need to store the historical data in the database? Do you need to store the result? If you are just trying to output a result and not store it then SSIS/SQL is not the right solution. – Nick.Mc Sep 12 '13 at 08:45
  • @ElectricLlama... Yes, right now I am getting only one value,but this is just sample project, my main aim is to get series of values from web service and to store them all in database. – Reshma Sep 12 '13 at 09:33
  • OK, so so far we have established that you can insert your XML into a datbase, right? The next step is t oconvert that XML into a number, correct? – Nick.Mc Sep 12 '13 at 11:16
  • here is a link to someone assiting to do this task: http://stackoverflow.com/questions/16076321/xml-from-webservice-to-sql-server-using-ssis – Nick.Mc Sep 12 '13 at 11:27
  • @ElectricLlama...I went through the given post, they are making use of xml source in data flow, whereas I have done updating database in execute SQL task itself and I don't want to make use of data flow control, is it not possible to work without Data Flow control to perform this task or shall I remove execute SQL task and work for Data Flow control, I have for time being saved the xml file on local drive and with the help of XML task I have successfully converted the data I want and updated the database but I was trying to take result in variable from web service task rather than file. – Reshma Sep 12 '13 at 12:43