November 12, 2012 at 4:20 am
Hi 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:- None
ConnectionType:- OLE DB
SQLSourceType:- Direct input
SQL Statement:- exec OperationalBatchGenerateFile ?, ?
IsQueryStoredProcedure:- false
BypassPrepare:- True
Parameter Mapping:
User::BatchID, Input,SHORT,0
User::TimeStamp,Inout,NVARCHAR,1
November 12, 2012 at 9:38 am
-Create a variable to hold your return value.
-Change your ResultSet: None to SingleRow
-Map your variable to your result set
November 13, 2012 at 4:52 am
Thanks 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
ALTER PROCEDURE [dbo].[OperationalBatchGenerateFile](@BatchID int, @timestamp nvarchar(50))
AS
BEGIN
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 OFF
END
in Mgmt Studio, if you run
EXEC OperationalBatchGenerateFile 1234, '20121113_104300'
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
DECLARE@return_value int
EXEC@return_value = [dbo].[OperationalBatchGenerateFile]
@BatchID = 10111,
@timestamp = N'20121113_114300'
SELECT'Return Value' = @return_value
GO
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()
November 13, 2012 at 5:10 am
Quick 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 tab
Error: No result rowset is associated with the execution of this query.
So it am still not getting the return value
November 13, 2012 at 11:13 am
Try 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.
November 13, 2012 at 3:01 pm
The solution is to write the SQL statement as
EXEC ?= usp_myStoredProc ?,?
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 offered
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply