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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy