SSIS staging table - ok to rebuild every time?

I have a package in which I need to load a flat file to a staging table, then perform an upsert to a production table. Actually, this has to happen a number of times for different files with different layouts. So we're talking maybe 15-20 staging tables.

I could create each of these staging tables as a permanent table in the开发者_StackOverflow中文版 db. But there's a part of me which likes to keep my schema clean, so I was entertaining the idea of bookending the package with a create table statement at the beginning and a drop table at the end.

What's the downside, or cost, of doing this?

---

One issue that you'll have to plan for is failures. If you create the table at the beginning of package execution and the package fails before it reaches the drop-table task, you will leave a staging table in the database.

I've used this approach before, and found that it was useful to create a maintenance job that cleans up tables periodically. In my case, I created tables with unique names. If you recreate a table with the same name, you must plan for the case when the table already exists.

If you don't have a permanent staging table, you lose information (data in the staging table) that may be helpful when you are problem solving.

You might consider a separate database that contains all of your staging tables. At the start of a package, a task can prepare the staging table by deleting the information from the previous execution. Then, the package loads the data for the current run.

---

A temporary staging table needs created which implies ddl_admin or db_owner rights.

Permanent tables require standard CRUD rights (except for TRUNCATE TABLE if you use it)

Otherwise, as per bobs' answer

---

Going along with bobs response, in some of my ssis flat file source packages, I have used staging tables. After a successful transform and load, the package cleans up staging by moving the data to archive. A pro to archiving data is that you can see how your extract package manipulated the data (if you are manipulating data in extract that is). A con is that you already have a copy of the flat file saved so archiving your staging table would just be redundant.