ADF Copy Activity with USP as source and Azure SQL server as sink

Question

Hello All,

I have a weird situation. I am trying to run a USP (which writes into the local temporary table and drops the temp table at end of stored procedure), therefore there is no output table the stored procedure writes to. When we run the Stored procedure EXEC dbo.test @Param1 =1 , it gives data in SSMS window. Now I am trying to load this data ( by passing the same parameters above and trying to run the stored procedure) from one SQL on prem server to another Azure SQL server.

I do not have permissions to modify or change the USP. The number of rows output from the stored procedure is about 8k.

I tried couple of solutions and nothing worked.
When I just try using copy activity and use Stored procedure as input and pass parameters, I get errors saying it cannot find the local temporary table while running

"
Failure happened on 'Source' side. 'Type=System.Data.SqlClient.SqlException,Message=Invalid object name '#outputtable'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=208,Class=16,ErrorCode=-2146232060,State=0,Errors=[{Class=16,Number=208,State=0,Message=Invalid object name '#outputtable'.,},],'"

When I try using lookup activity and pass it to an array with set variable, it is not able to handle 8k rows and says the number of rows exceeds the limitation of Lookup activity.

If anyone has a better solution pls help or if I should use a different activity also, pls let me know. Can I use data flow to run USP as source and write it to a SQL table? Would that work?
Thanks for all your help

Answer 1

Hello @N2120 ,
Thanks for the ask and using the Microsoft Q&A platform .

I wanted to repro the issue , but then thinks just worked fine for me , I am sharing what I did , may be you tell me how your scenario is different .

My proc looks like this .

CREATE proc testproc @id int
as

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
BEGIN
CREATE TABLE #Results ( id int , name varchar(100) )
END
INSERT INTO #Results select PersonID,Name from [dbo].[customer_table]
Where PersonID = @id
SELECT * from #Results
drop table #Results

exec testproc 3

I did pointed the ADF with source as the Proc as shown below and it worked just fine .

Hope this helps .

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

Answer 2

I was able to resolve this issue by using ODBC Linked Service Instead of SQL Server and using Query option in Copy Activity while moving data from On Prem server to Azure .
Thanks for your help.