SSIS Design Question
I have a Stored Procedure that I am running via SSIS that does a Distributed Transaction. It inserts and updates data one on Server1. Based on that update/insert, it creates a temp table. The temp table is used within the update statement to update table on Server2 via a Linked Server. The problem is that the update via the Linked Server is extremely slow.
I am thinking of breaking it down via SSIS so that each SQL runs on each server which would be much faster. This seems like it would work but I am just learning SSIS. So if I have an EXECUTE SQL that executes a SP or SQL which will do the inserting and updating data one Server1, and then if that is a success I proceed to the next step.
The issue is within the one SP I was using a Temp table that holds the keys that will be the input parameter of the SQL that is executed on the Server2. A temp table will not work since the temp table will not be available to both servers. So how can I hold the data, some sort of table, from one Execute SQL as the input to another Execute SQL?
Table value parameters are read-only and input only. I could put it in a physical table and then delete all rows in the table when all tasks have completed. Since I am new to SSIS, do you have any other ideas that you can think of in storing the data from one insert/update on Server 1 as input to Server2?