I am trying to receive the return value (@ReturnValue) from a stored proc
while using the SSIS Execute SQL command and map it to a package variable.


I can run in a SQL query window the following commands

DECLARE @ReturnCode INT

exec @ReturnCode = usp_Import_Entries 'EMD'

Print @ReturnCode

This will return an integer.

I have tried to run the query in SSIS execute task without any luck. Here
is what I am sending as the task command:

exec ? = usp_Import_Entries 'EMD'

and I have set up a paramater mapping with the following option


Variable name: User::iSP_EntryReturn
Direction: ReturnValue
Data Type: Long
Parameter Name: @ReturnCode

I have tried to declare User::iSP_EntryReturn as an int16, int32, and int64
with no luck

When I run the proc the following error occures:


Error: 0xC002F210 at Load Entries Table, Execute SQL Task: Executing the
query "exec ? = usp_Import_Entries 'EMD'" failed with the following error:
"Value does not fall within the expected range.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.


Also note that I can run the task with the following command without
problems as long as I do not have any parameter mappings.

exec usp_Import_Entries 'EMD'"

Any thought on how I can get the return value stored from the task?

Thanks in advance,

--
Stewart Rogers
DataSort Software, L.C.

Hello Datasort,

have a look here. We do pretty much everything with this task

The ExecuteSQL Task
(http://www.sqlis.com/default.aspx?58)

allan

Thanks to Allan Michell site at (http://www.sqlis.com/default.aspx?58) I was
able to figure out the problem.

The issues is that the last value

Parameter Name:

should read 0 not @ReturnCode. I assume this is a zero based array.


--
Stewart Rogers
DataSort Software, L.C.


"Datasort" wrote: