InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 05:16:58 GMT20http://www.sqlservercentral.com/Forums/Topic1383600-364-1.aspxThe solution is to write the SQL statement as[code="sql"]EXEC ?= usp_myStoredProc ?,?[/code]and set the parameters as Param0 = ReturnValue, Param1 = Input, Param2=Input and assign Param0 to the variable you want to carry through.Thanks to everyone for the help offeredTue, 13 Nov 2012 15:01:37 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1383600-364-1.aspxTry making the variable an object type. Although you are really only returning a single value, the task views it as a set that might contain more than one column value.Tue, 13 Nov 2012 11:13:47 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic1383600-364-1.aspxQuick update.One problem I was having was that I had not renamed the default resultset to 0 to return the first column.Now I get the error message in the Progress tabError: No result rowset is associated with the execution of this query.So it am still not getting the return valueTue, 13 Nov 2012 05:10:23 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1383600-364-1.aspxThanks Daniel, I am struggling with this concept... I did as you suggested and get processing errors..Here is the storedProc that is being called from the SQL task[code="sql"]ALTER PROCEDURE [dbo].[OperationalBatchGenerateFile](@BatchID int, @timestamp nvarchar(50))ASBEGIN SET NOCOUNT ON DECLARE @msg nvarchar(255) DECLARE @Cancel as int SET @Cancel = 0 /* check if the batch ID exists */ IF (SELECT count(*) from opsBatch where batchID = @BatchID) = 0 BEGIN SET @msg = 'Batch ID ' + cast(@BatchID as nvarchar(5)) + ' is not recognised' raiserror(@msg,1,1) SET NOCOUNT OFF RETURN -1 -- quit the routine with error: This should never happen because the data has been extracted from the batch id END /* if we have passed all of the above tests then we can update the batch and the lines */ BEGIN TRANSACTION -- all pass or all fail UPDATE opsBatchRecord SET mailStatus = 'Exported', MailStatusDate = cast(convert(nvarchar(20),getdate() ,112) as datetime) WHERE BatchID = @BatchID UPDATE opsBatch SET Status = 'Exported', ExtractFileName = cast(@BatchID as nvarchar(5)) + '_' + @timestamp + '.txt' WHERE BatchID = @BatchID COMMIT TRANSACTION Return 99 SET NOCOUNT OFFEND[/code]in Mgmt Studio, if you run [code="sql"]EXEC OperationalBatchGenerateFile 1234, '20121113_104300'[/code]Then the procedure runs but does not return a results set.If you use the right click->Execute on the sp and you get the following script[code="sql"]DECLARE @return_value intEXEC @return_value = [dbo].[OperationalBatchGenerateFile] @BatchID = 10111, @timestamp = N'20121113_114300'SELECT 'Return Value' = @return_valueGO[/code]then the return value is 99 or -1 depending on what happens. It is this value that I want to interrogate.Can I get at this return value without a results set or alternatively pickup the raiserror()Tue, 13 Nov 2012 04:52:11 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1383600-364-1.aspx-Create a variable to hold your return value.-Change your ResultSet: None to SingleRow-Map your variable to your result setMon, 12 Nov 2012 09:38:13 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic1383600-364-1.aspxHi guys,I am very experienced in T-SQL but am still fairly new to SSIS and am trying to run before I walk. I have an Execute SQL task which runs a stored procedure that updates some database records. It takes two input parameters which are supplied in package variables. so far so good - the code runs and succeeds.The storedProc does some validation (e.g. does the ID passed in exist in the database) and returns -1 if there is an issue or 99 if it runs to completion as expected. There is no other results set.How do I configure the SQL statement and the results set.Current config is as follows:Execute SQL Task:ResultSet:- NoneConnectionType:- OLE DBSQLSourceType:- Direct inputSQL Statement:- exec OperationalBatchGenerateFile ?, ?IsQueryStoredProcedure:- falseBypassPrepare:- TrueParameter Mapping:User::BatchID, Input,SHORT,0User::TimeStamp,Inout,NVARCHAR,1Mon, 12 Nov 2012 04:20:15 GMTaaron.reese