Store procedure with output param in lookup

Question

Hi,

I am using Lookup activity to execute store procedure with an output parameter. But I have noticed if the store procedure doing Insert, then

"message": "Failure happened on 'Source' side. ErrorCode=SqlInvalidDbStoredProcedure,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The specified Stored Procedure is not valid. It could be caused by that the stored procedure doesn't return any data. Invalid Stored Procedure script: '[Staging].[poc_test]'.,Source=Microsoft.DataTransfer.ClientLibrary,'",

But the same activity works, if I comment out the Insert statement. Anyone can share some knowledge on this.

Test Store Procedure

Alter Procedure staging.poc_test
@Tai lieu van ban int output
as
Insert into staging.poc_test_table
select GETDATE() as Date
set @Tai lieu van ban = @@ROWCOUNT

Answer 1

Hello @rajesh kumar ,

Thanks for the ask and welcome to the forum .
The lookup activity expects a return dataset but in your case you are not returning a dataset but a parameter . Please update the proc as below and it should work .

Alter Procedure staging.poc_test
as
Insert into staging.poc_test_table
select GETDATE() as Date
select @@ROWCOUNT

Do let me know if you have any further queries .

Thanks Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members