Loading Temp Table in SSIS from source on differenct Server

Question

How can i Load Temptable on Server B by reading data from Server A in Dataflow task.

Answer 1

Why do you need to write to a temp table? You can simply perform any transformations you need on the buffer in memory and then write out to the final table. It is notoriously difficult and usually not worth the effort to write out to a temp table in SSIS. See this blog entry from Jamie Thomson about how to do this, but take note of his warnings at the bottom of the post:

http://consultingblogs.emc.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

Answer 2

The short answer is don't do it. SSIS & Temp tables don't mix. There are ways around the problem but they aren't maintainable or scalable. Use a physical table and truncate it at the start of the task flow.