Delete records in destination table

Short Question: How do I delete all records from a destination table prior to appending new data to that table?

I am working with a SQL database that was migrated from MS Access. All relationships, primary keys, and identity columns have been set identically to the MS Access database values. The MS Access database is still being used as the database of record until the SQL database is fully functional with front-end, etc.

I want to delete the information stored in all the SQL tables, and then append the MS Access values to the SQL tables. I was able to write delete and append queries in MS Access to correctly transfer data to the SQL tables. However, I would prefer doing this through SSIS because I have several other sources of data to move to a SQL Server database and most of those other sources are not a MS Access database.

Due to relational entegrity settings, I need to delete the records from 8 tables in a specific order. I have tried independent control objects for each of the 8 tables with data flow objects of either "OLE DB Command" or "OLE DB Source" with the SQL command as "Delete From TableName". Results of the debug indicate everything is "green" but no records were deleted fromt the tables.

Maybe you could just generate scripts for the entire DB in Management Studio, and create a new database in SQLServer . You could use this new DB as the destination.

For the actual migration of data, you could use SSIS.

|||

Can't you just use an Execute SQL Task (or just use T-SQL through the management studio) and truncate the tables in the order of constraints?

Truncate Table a; Truncate Table b; etc...

|||

EWisdahl is correct. Use an Execute SQL to run the DELETE FROM statement, then your data flow after that.

Execute SQL -> DataFlow