Hi ,
I am trying to access the one stored procedure from execute SQL task with 2 input and one output parameters. And the out put of the stored procedure here is the Print statement (the query what i want to use as a variable in SSIS in the DFT). I am not able to configure this in execute SQL task. Any suggestions will be appreciated. Many thanks in advance. More details below.
Step 1 : A table with 2 columns
create table dbo.test
(
id int,
name varchar(10)
)
--- 3 rows inserted
insert into dbo.test values (1,'john')
insert into dbo.test values (2,'Pat')
insert into dbo.test values (3,'roll')
---- Procedure created which will return the Select statement as a results which i will use in the DFT once we map to a variable in SSIS.
Create procedure dbo.PrintSQL (@id int, @DeezNutz nvarchar(100) , @Query Nvarchar(max) Out)
AS
Begin
Set @Query ='
Select * from dbo.test where id =' + CAST(@id AS Nvarchar(10)) + ' AND name = ''' + @DeezNutz + '''
'
Select @Query as Query
END
Step 2 : Not i have execute SQL task and trying with the below expression.
exec dbo.PrintSQL ?,? , ? OutPut
Results set property to NONE.
In the parameter mapping window :
added 2 input and one output parameter with proper data types.
its not working as expected. my Output variable always having the value as NULL when i look for the Debug-->Window-->Local tab for Local variable values .
Thanks.