Execute SQL Task Error: no result rowset associated...

I am getting the following error when I execute my sql task:

An error occurred while assigning a value to variable "NullVar": " NO result rowset is associated with the execution of this query. "

I am executing a SP that has one input & one output parameter. The output parameter is returning a single row for debugging if the sp failes.
I tried using Jamie's method:(http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx) to get it to work but keep getting the above error. I have the following variables:

sqlSource (string) := Exec RBCprcsInsertWmsInvTransactionRecords '" + (DT_WSTR,10 ) @.[User::SnapShotDate] + "', NULL"
NullVar (string)

In the execute sql task, I set the ResultSet to single row. I set SQLSourceType = variable & sourcevariable = user::SqlSource. In the result tab, I added a result set, NewResultName with the variable user::NullVar. I tried different configurations with the parameter mappings but nothing seemed to work. I didn't know if i still had to use this if I am using the sqlSource variable to drive the task.

So I am not sure what I am missing here. Anyone have any suggestions?

Thanks!
John

I would try to catch the SP output via parameter mapping in the execute sql task.

For that, you have to change ResultSet=None; delete the result set from result set tab; and add 2 entries in the parameter mapping page; these 2 entries have to be in the same order you are using the parameters in the Exec t-sql command. If you are using OLE DB connection; the parameter name in the parameter mapping page should be 0 and 1.

The SQLSourceType should be DirectInput and the SQLStatment: EXEC RBCprcsInsertWmsInvTransactionRecords @.yourInputParameterName = ?, @.YourOutputParameterName = ? OUTPUT

|||

Rafael,

I was missing how to correctly specify the parameters. Thanks for giving me the correct syntax! I had tried something similar to this before using the variable to pass the sql statement but didn't have it quite right.

Can i add one more question onto this? Do I have to turn on logging inorder to capture this output variable to a log file?

Thanks again!
John

|||

laker_42 wrote:

Can i add one more question onto this? Do I have to turn on logging inorder to capture this output variable to a log file?

Package logging would give you logging information about package execution; I don’t think is the way to go for getting the value of a SSIS variable written into a file. I cannot think on a quick way to do so; but perhaps you could use a script task.

|||Ok, i will do some more digging on that. Thanks for your help!

John