Executing a procedure with input and output parameters from SSIS execute SQL Task

Krish 81 Reputation points
2021-06-29T18:49:32.867+00:00

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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,702 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Olaf Helper 47,436 Reputation points
    2021-06-30T06:11:58.893+00:00

    And the out put of the stored procedure here is the Print statement

    Really as PRINT in the SP; I don't see one? No, in that case you won't get it working, you can not get the PRINT output (=InfoMessage) with a SSIS Execute SQL task. You have to use real Output parameter in the stored procedure.

    Results set property to NONE.

    I see a SELECT statement in your SP. To get it you have to define the result set property to get the result.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.