Clearing Previous Data

Guys
Struggling with how to create the following. We upload daily delivery data from CSV to a MSSQL Data base nightly, however due to various factors, errors may be made in the compilation of the daily CSV and a correction needs to be applied.
In an ideal situation, an update would do the trick - however in some cases there may have been EXTRA records added with the previous upload that need to be deleted. The process I considered using was deleting the whole previous day and then just a clean reload of the corrected file.
Each CSV only holds one complete day
The number of records is small < 80 records generally

I just can't figure how to delete a CSV recordset based on the day field AND upload a new set at the same time in the Writer
Can you assist of give some guide on this?
Thanks
Richard

You can use sql after to delete the data. You can also use variables in sql after.
If it is no clear please post some screenshots so we can uderstand your process better

Mike

The issue is that the deletion must take place before the new upload - attached a screenshot of what is currently in place
However - if when updating with a new CSV following a possible error in the original data - and a record from the previous batch is no longer required - then this routine fails as it only updates records and does not remove the extra incorrect record

If the record only requires a change in amount or something similar then this works fine

I assume that you are running transformation from the package.
Create table in the database and use it to store execution status.
When execution fails you set status to failed. Otherwise set it to success.
Before executing the transformation check the status using check sql action
If it is failed delete the data.
I hope this information is useful for you.

In addition to Mike's suggestion



Please keep us posted on your progress