question

kirankumar-3620 avatar image
0 Votes"
kirankumar-3620 asked OlafHelper-2800 answered

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

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, @name nvarchar(100) , @Query Nvarchar(max) Out)
AS
Begin
Set @Query='
Select * from dbo.test where id =' + CAST(@id AS Nvarchar(10)) + ' AND name = ''' + @name + '''
'
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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.