1

I was trying to insert data from variable into database via SSIS, I made use of Execute XML Task, below is the screenshot, enter image description here and also created the stored procedure in my sql server to insert data and Data is my user variable which stores the result in xml file format from web service task, but this SQL EXECUTE Task is not executing

enter image description here this is parameter mapping page Even if I replace the sql statement by query as "insert into dbo.Data_Result(Result) values('1')" and remove the mapping parameter still it shows the error in execution.

No...task does not fire, it doesnot show any errors actually..sorry for wrong explanation, my web service task executes successfully but Execute XML Task does not executes.

Below is my flow enter image description here In web service task I have used my simple web service to add the numbers and returns the data in variable, that variable I have to use in Execute XML Task to enter that added result in sql Database.

Reshma
  • 864
  • 5
  • 20
  • 38
  • What's on the Parameter Mapping page? Also is there a space between `insertToTable` and `?` ? – mr.Reband Sep 10 '13 at 13:23
  • @mr.Reband...I have given up the parameter page and there is no space between procedure name and ?, in parameter mapping page User::Data is my variable that store the added result from web service task in control flow and @ ResultData is the parameter used in stored procedure. – Reshma Sep 10 '13 at 13:34
  • @mr.Reband I have tried with both space and by eliminating space between stored procedure name and ? mark, but it shows red cross mark while execution. – Reshma Sep 10 '13 at 13:35
  • Define "it's not executing" The task doesn't fire or it fires and fails? If it fails, please copy and paste the errors into the question. You can click the Edit button to update the question and add this information into it – billinkc Sep 10 '13 at 13:44
  • Thanks for updating. Do you have a precedence constraint on the task? Could you pop a screen shot of the control flow, maybe that will shed light on why the Execute SQL Task is not firing – billinkc Sep 10 '13 at 13:51
  • Parameters for OLE DB connections should have ParameterName = an integer value, zero-based – mr.Reband Sep 10 '13 at 13:53
  • @mr.Reband...but variables are string based I think and what my main aim is to return the added result from web service in any variable and to insert that added result in database. – Reshma Sep 10 '13 at 13:58
  • See #9 in this link: http://technet.microsoft.com/en-us/library/ms140355.aspx – mr.Reband Sep 10 '13 at 14:09
  • @mr.Reband. according to one solution given bye one of frnd, we can create a new variable and write an expression in its expression box and then use that newly created variable in Execute SQL Task, but I am still unable to resolve the issue, can you please follow the link [http://stackoverflow.com/questions/18691959/inserting-data-into-sql-server-from-variables-via-ssis] to check out the issue I am facing to do that task.I have explained the issue in screenshots. – Reshma Sep 11 '13 at 05:51
  • Sorry, can you confirm you tried what was mentioned in #9 in my previous link? You are using an OLE DB connection which requires that the variableName (which you have in your screenshot as `@ResultData`) should be an integer value, namely `0`. – mr.Reband Sep 11 '13 at 13:33

1 Answers1

0

Within the Parameter Mapping window, the Parameter Name needs to contain an integer value, starting with zero, not the name of the parameter as defined in the stored procedure. This integer value maps to the question mark in the SqlStatement property.

You have one parameter to map, so you should use the value zero in the Parameter Name. The integer value used should map to the position that that parameter appears in the call to the stored procedure.

For example, if you add a second parameter to the stored procedure, you would first add another question mark to the SqlStatement property and then add a second parameter in the mapping screen with a value of 1 for the Parameter name.

Brian
  • 334
  • 4
  • 16