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


