I have a couple questions regarding Data Migration. We are migrating data from our main SQL Server 2005 envirment to a SQL Server 2005 (or 2008) Express database. The latter database is used as a backend for a Web Server application. The data is used for mainly dropdown choices for fields like municipality and school district codes. The data doesn't change very often but we would like our Web Server application to stay up as much as possible. Normally when doing data migration in the past we have truncated the table being updated (or created a new table if it wasn't present) and then completed a source query, data transformation, and finally written the data to the destination table. The problem is that if something failed after the truncate, the applications that use the destination tables are down until the problem is corrected and the SSIS job can be re-run. Is there a better way to structure our packages so that the original table could be restored if the job fails? Our reasoning for this is that it would be better to have slighlty old data than a down Web application. The other question that I have is: What is considered better, a push or pull of the data? Our current Preparation SQL Tasks are similar to the following: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WCFIELDVALUES]') AND type in (N'U')) TRUNCATE TABLE [WebPresentationDev].[dbo].[WCFIELDVALUES] ELSE CREATE TABLE [WebPresentationDev].[dbo].[WCFIELDVALUES] ( [FIELDNAME] varchar(18) NOT NULL, [FIELDVALUE] varchar(4) NOT NULL, [EFFDT] datetime, [EFF_STATUS] varchar(1) NOT NULL, [LONGNAME] varchar(30) NOT NULL, [SHORTNAME] varchar(10) NOT NULL ) GO

There are tons of ways to keep your table "available" in the event of a bad load. You could load to a table with identical structure, and if the load is successful, drop the old table and sp_rename the new one. You could perform inserts, updates, and deletes on the table instead of a full truncate and load. Talk to me now on

Thanks for your response Todd. Would you recommend the rename as a first choice, i.e., the defacto standard to approaching this challenge?

Probably not... but it really depends on what's easy for you and what is possible. It may not work for you if you have schema bound objects in your database. The standard is probably the "update" type scenario. But the logic there is definitely more complex than a truncate and load, or swapping in an offline-loaded table. It's just less disruptive to the structure of the database and applications that use it. Talk to me now on