running on: SQL Server 2000
I am exporting data (csv format) from test DB and import into live DB, so everytime I have to truncate live table and import file. If ther's a key binding, I have to drop the key first, do import, add key back. This task is too inconvenient. How should I improve this? Thanks.
If you have referential constraints on the data that you're importing AND you need to truncate the data, then you will have to drop and recreate the key, there's no way around it. However, I think you're asking if you're required to truncate the data as part of an import? If so, the answer is no. There's no requirement that you truncate the data. As a matter of fact, the best way to create a scalable ETL process is to only move the data that has changed rather than doing a complete reload every time. This is achieved any number of ways. One of the simplest is to maintain an UpdateDate column on the source data and only retrieve the data that has been changed. You'll have to come up with mechanisms to support deletes as well though. There are a number of other ways around this as well.
Hi,
I am not very clear what you trying to achieve. However I think that is what you wanted.
Create - Execute SQL Task You can write 'Truncate statement' and other droping key or whatever you want.
Then add that Task to process before you transfer from CSV file to DB by using Work Flow Properties.
I hope it helps.
