0

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 enter image description here

If I place the query in double quotes then following error is shown enter image description here

And if I remove User from User::Data Variable and place the query in double quotes then I get following screen enter image description here 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 enter image description here

------------Updated question according to other query----------

Here is the picture of my whole work flow

enter image description here

This is what I have did inside the for each loop container under collection tab

enter image description here

And this below setting I have done between Variable mapping tab,

enter image description here

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,

enter image description here

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,

enter image description here

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.

Reshma
  • 864
  • 5
  • 20
  • 38
  • 1
    Could you post printscreens of your control and data flows? I think you may be using sqltask or oledb command wrong way. – AdamL Sep 09 '13 at 11:16
  • @frikozoid.. Can you please follow the link [http://stackoverflow.com/questions/18720095/error-on-executing-sql-task-in-ssis] to check my Execute Sql Task setting, here I have done through inserting stored procedure and also the another trial I have explained on the same page using variable but still facing an issue in query..I have explained both the issue in screen shots. – Reshma Sep 11 '13 at 05:56
  • What happens when you drag `User::Data` from the top left window into your expression and evaluate - does it work? what if you put `"test" +` in front of it - does it work? – Nick.Mc Sep 11 '13 at 06:48
  • I've also changed my post below and altered the format - try it. I've added square brackets to the expression – Nick.Mc Sep 11 '13 at 06:50

1 Answers1

2

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

  1. Create a new SSIS variable of type string
  2. In properties, set it's 'Evaluate as Expression' to TRUE

  3. In properties, click on 'Expression' and build an expression as follows:

    "INSERT INTO Data_Result(Result) VALUES ('"+@[USER::my_data]+"')"

  4. 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:

  1. Drop a execute SQL task on your page. Set it's connection
  2. With the execute SQL task highlighted, go to properties and click expressions
  3. Pick the property 'SQLStatementSource'. In the expression pick your variable that you built in step A and press evaluate.
  4. Press OK on everything

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.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • ..I am not able to build proper insert command, I have written query as follows "INSERT INTO Data_Result (Result) values ('@USER::Data')", then it's giving me error on evaluating expression that attempt to parse the expression failed.The expression might contains an invalid token, an incomplete token, or an invalid element. It might not be well formed or might be missing part of required element such as parentheses whereas USER::Data is my User variable, even if i rewrite the values by any Number, it gives the same error. – Reshma Sep 09 '13 at 13:35
  • I have added more detail to my reponse. Note you need to use an execute SQL Task, not a data flow task. – Nick.Mc Sep 10 '13 at 23:59
  • ..I have created the new variable as explained by you but still unable to resolve the issue as explained in the above question asked by me, I have done required edition in it with necessary screen shots. – Reshma Sep 11 '13 at 05:42
  • ..it worked but unable to store data into database in proper format, please follow the below link, [http://stackoverflow.com/questions/18738310/conversion-of-data-through-xml-task-vis-ssis] – Reshma Sep 11 '13 at 10:13
  • @ElectricLlama.."INSERT INTO Data_Result(Result) VALUES ('"+@[USER::my_data]+"')"..this query insert only one value but what if I want to take no of elements in my_data user variable and wants to insert the array of values in database then what should I do?..I am also making use of for each loop container but unable to store data into database looping through variable , also my web service is now giving more than one data into xml file. – Reshma Sep 17 '13 at 07:47
  • If you have mutliple web service calls and multiple XML files to insert you just use a for loop to loop around the web service and insert steps. If you have multiple nodes in the XML fiel to shred out, that is a completely different story. Everything I've seen says insert the XML into the database, into an XML data type, then use xpath functions on the XML column to shred your data out. – Nick.Mc Sep 17 '13 at 11:28
  • @ElectricLlama...I have single web service which evokes one single method and in that method more than one value is returned, if I want to insert one single value in database then I can do it as explained by you earlier but now if I want to insert two values through insert query in Execute SQL task then I am not understanding how to make use of array to store xml data and loop through it, [http://stackoverflow.com/questions/18822208/retrieving-node-values-from-xml-file-and-storing-the-data-into-database-via-ssis], this link shows my xml file and I have also updated the question to give u idea – Reshma Sep 17 '13 at 11:46
  • You are getting _one_ XML file. Insert that _one_ XML file into the database as you have already done. Make sure you insert it into a field of type XML. Now use the sample answer in this question: http://stackoverflow.com/questions/17881741/extracting-values-from-xml-field-in-ms-sql-server-2008 to extract any number of pieces of data from your one XML file. Where do you want to put this data? insert into another table? – Nick.Mc Sep 19 '13 at 07:13
  • @ElectricLlama...my issue is different, initially I was inserting only one value in one column, that I did but now I want to insert multiple values in more than one column, I guess I need to make use of ForEach loop container but I am unable to loop through it to take multiple data and to put in database. In foreach loop container, I have placed my execute sql task now,I making use of Foreach loop container as updated in above question,plz have look at it. – Reshma Sep 19 '13 at 07:27
  • OK lets leave this question alone now and I'll take a look at your other question. – Nick.Mc Sep 19 '13 at 11:05
  • @ElectricLlama..sorry to confuse you but for current time,my both question points to same problem, Initially my web service was given single result of addition of no that I can save into database by the query you gave in EXECUTE SQL TASK, but now I have changed my webservice function to give two values that is name and birthdate and I want to save both of them in my new table with two columns. For this purpose I am making use of ForEach Loop Container, now I have declared two variables one for storing name and other for storing birth date. – Reshma Sep 19 '13 at 12:16
  • @ElectricLlama.. but now problem is I am getting whole of the XML file into one single variable as follow {\r\n\r\n Rahul\r\n 1988-09-23T00:00:00\r\n}..I hope you got my problem, I want to store that name and birthdate in different variable via foreach loop and then insert that data stored into database via Execute SQL Task. – Reshma Sep 19 '13 at 12:17
  • @ElectricLlama.. Can you please tell me the solution for the problem of I am trying to create the Integration Service Catalog in SSMS for deploying the SSIS package and when I am trying to configure it then it is saying the password doesn't meet the requirement of password filter dll, I am not able to find the proper articles to help me with this – Reshma Sep 30 '13 at 12:21
  • Please ask a new question for that – Nick.Mc Sep 30 '13 at 23:55
  • @ElectricLlama.. I have already asked new question for that, Please refer the following link [http://stackoverflow.com/questions/19089180/unable-to-connect-to-integration-service-through-ssms/19093795?noredirect=1#19093795] – Reshma Oct 01 '13 at 04:53