I have consumed simple web service for addition of two numbers and I am getting the result in xml format that I have stored in variable supposed named as my_data, now I have to insert that result in my sql table named as Data_result by writing query in oledb source editor, but unable to insert data using variable.I just want to store all the result calculated by my web service in sql table.
Since I am newbie to SSIS, any help is greatly appreciated
When I am trying to insert the data by the following query command:
"INSERT INTO Data_Result(Result) SELECT '"+@USER::my_data+"'"
It's giving error:
Error at Data Flow Task [OLE DB Source [55]]: No Column Transformation was return by SQL Command
--------Updated explanation of errors based on the solution given
Error for executing query If I place the expression without the "" quotes then I get following error
If I place the query in double quotes then following error is shown
And if I remove User from User::Data Variable and place the query in double quotes then I get following screen although the expression evaluates but after proceeding further on this evaluated expression when I am trying to search for the variable in expression column of Execute sql Task, then I am unable to locate the newly created variable, as shown below
------------Updated question according to other query----------
Here is the picture of my whole work flow
This is what I have did inside the for each loop container under collection tab
And this below setting I have done between Variable mapping tab,
And in below screen shot, I am using Execute SQL Task to enter my data obtained from web service task into database using an insert query, but unable to fire proper insert query,
And below is my XML file,
<?xml version="1.0" encoding="utf-16"?>
Reshma
1988-09-23T00:00:00 ,
This name and birthdate I received from web service I want to insert that into database table
--------------Updated question for foreach loop container--------
Below is the foreach loop container I am using,
But still I am not getting node value into variable and also I am suppose to make use of object type variable or it can work with string type of variable.
I suggest you build this SQL statement in another variable, as explained here:
http://consultingblogs.emc.com/jamiethomson/archive/2005/12/09/2480.aspx
Then use that variable in your execute SQL statement
Normally you would try and parameterise the query but in this case (as in many others) it won't work.
Here are some more detailed instructions:
A. Generate the dynamic SQL
In properties, click on 'Expression' and build an expression as follows:
"INSERT INTO Data_Result(Result) VALUES ('"+@[USER::my_data]+"')"
Press Evaluate and verify that the output is valid SQL (you may even wish to paste it into SSMS and test)
B. Get an Execute SQL task to run your SQL:
Now whatever change occurs in my_data will automatically be applied to your execute SQL task
Try that for starters and get back to me.