October 5, 2010 at 12:07 pm
Hi SSIS Guru,
Currently, we have our own internal app to pull data from the internet nightly to serverA. On serverA, there is a huge stored precedure doing update, insert and delete by joining lots of tables on serverB via synonyms over LINKED SERVER. However, there are only 3-4 tables on serverA used to join tables on serverB.
I am rewritting this process to eliminate linked server and use SSIS instead of stored procedure. What is the best option to bring tables from serverA to serverB? Should I create staging tables on serverB and refresh tables with TRUNCATE or use temp table instead?
Thanks much,
Attopeu,
October 5, 2010 at 12:10 pm
I prefer staging tables for the simple reason that if something fails, you can just select from it and see which data might be causing the issue instead of filtering from the source again.
edit: Not to mention that the source data may have changed by the time you try to find the offending row..meaning you're dead in the water until it happens again, unless you dump it to a staging table.
October 5, 2010 at 12:28 pm
I'm in agreement with Derrick. I vote for Staging tables.
Also, a real staging table would not add overhead to Tempdb as a temp table would do.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply