I'm writing an ssis package that imports a number of access databases nightly. Each database has the potential to have a slightly different schema. Since I'm creating the tables than doing a direct table insert using execute sql tasks inside of a for each loop container the schema differences cause errors. I've upped the max error count so that the package can take errors and continue running. This leaves me with some databases that have incomplete data which I would prefer to drop.
I attempted to make an event handler that would drop the database; however, at the point that the event handler is called the database is still in use. Is there another way that I could drop the database if there is an error?
Best Answer
Not 100% sure what you are attempting here, but you could record the names of "partial" databases in a table in SQL and then run a separate process after the fact to drop them.