How To Copy Specific Data from Main Server to Link Server Daily.
Posted on 2007-08-01
We have a "main" SQL Server 2005 with about 200 tables. We need to copy the data daily from 10 of these tables to a "2nd" 2005 SQL server outside our control. We've setup the (2nd) server as a linked server to the "main" server. For security reasons we can give the "main" server access to the "2nd" server but not the other way around.
The table structure on the "2nd" server is exact. Including PK, FK, Relations, Indexes; and must remain this way for reporting purposes.
I'm having problems with each of the methods I've tried so far:
SSIS: set to Export the data and delete existing files from "2nd" Server. I get an error that SSIS can't truncate the tables due to constraints on the tables.
Stored Procedure: I can't set identity_insert on the tables on a linked server.
Combo: I delete all records from 2nd server via stored procedure, then tried to setup an SSIS to export tables with append method to linked server. Problem is that SSIS is trying to export both orders and customer tables at the same time and throwing constraint errors. I can't find a way in the SSIS package to tell it to export all customers first then the orders.
Thanks for your assistance!!!