Tuesday, December 17, 2013

ReturnValue, OutputValue, ResultSet and InputValue in single SSIS Execute SQL Task

SP-

USE [BGBL]
GO
/****** Object:  StoredProcedure [dbo].[testsp]    Script Date: 12/17/2013 5:03:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testsp] (@p_Input varchar(50), @p_output int =null output )
AS
BEGIN

/*declare @p_Output1 int
declare @p_ReturnValue int
exec @p_ReturnValue =  testsp 'A',@p_Output1 out
select @p_Output1
select @p_ReturnValue */

select * from Dealer_Master where dealer_group=@p_Input
set @p_output=@@rowcount
return (select count(distinct core) as cntreturn from Dealer_Master where dealer_group=@p_Input)
END


---

SSIS

1) Place Execute SQL Task in control flow 
2) Create OLE DB connection (not .net connection)



No comments:

Post a Comment