Hello @Rohit Kulkarni ,
As called out before by @Nasreen Akter , lookup expect the dataset in return and so you were getting the error which you initially encountered , but it will work wih SQL procedure as I have tested that and sharing the details below .
I created a dummy table
CREATE TABLE [dbo].[t2]
[col1] [int] NULL,
[col2] [varchar](100) NULL
inserted some dummy data
INSERT INTO [dbo].[t2] values (1,2)
INSERT INTO [dbo].[t2] values (2,2)
INSERT INTO [dbo].[t2] values (3,2)
INSERT INTO [dbo].[t2] values (4,2)
Created a proc which takes ID as a parameter , delete the record and send the details of the record which was delete
CREATE PROC DELETE_proc_test
IF OBJECT_ID('tempdb..#sometable') IS NOT NULL DROP TABLE #sometable
select * into #sometable from [dbo].[t2] where 1 =2
INSERT INTO #sometable
SELECT * from [dbo].[t2] where col1 = @id
delete from [dbo].[t2] where col1 = @id
-- send the records deleted back
SELECT * from #sometable
Setup the copy activity , please pay special attention as to the import parameter . Also as copy activity expects a sink i used a blob as sink . you can use anything . The below animation will help you understand it better . The proc will also work using lookup .
Let me know how it goes .