SSIS Replace existing Records Problem

Can you help me solve the following?

Daily we are loading results to a table that has 5 key fields. It is a simple transformation from a csv file to the database table.

Occasionally the data is preliminary or incorrect in the csv file and needs to be reloaded (primary idenitfier here is date). I want to modify the existing Package to over-write/replace the existing records.

As should be obvious If I try to run the SSIS package with data from the same date I get a key violation and the package fails.

The date key field is in the csv and in the data table; it is not in the file name.

I am not sure of the best way to "check" to see if the data exists first, then delete it if it does, so a reload can occur. We are only talking about a couple hundred small records.

The date key field is in the csv and in the data table.

Thanks
Gregg

I am fairly new to SSIS and I do not have a ton of experience with SQL. This solution seems awkward but I did not have time to figure out from scratch how to program my own data flow component. So I primarily used the tools from the SSIS designer tool box:

I added a "header" record to the input file and a column of data that gave me the ability to use a conditional split data flow transformation. The header record has the current date of the data being loaded.

I then utilized a series of sequence containers to control the flow shown below. Each "-" is a new container.
-I loaded the header record to a "buffer" table. then
-Deleted all records in the actual table that matched the date in the "buffer" table. No records are deleted if there is not a date match.
-Then deleted the header record from the buffer table so when I loaded or reloaded I did not delete any records I want to keep.
-then used a conditional data flow to load all non header records to the table. This was an exact copy of the conditional flow except I changed the case of the flow to be the non header records.

I added a file watcher task for the trigger delivery and a file system task to delete the trigger (could also use it to delete the input file, but the process that generates the file just over writes it so it was not a concern)

In your SSIS package, you can calculate the key-date (use script component and write some VB.NET), then issue a delete statement deleting all records that match that date (by using the expressions feature of the SQL Command task to change the statement on the fly) (doesn't matter of they exist or not, a delete that matches zero records is fine, so you can execute this always). Then import your new data.

Thank you for your reply.

I presume you are saying that I could create the delete query using the input data instead of using the "buffer" table approach that I used.

I will consider this approach when we roll out the final solution.

Not actually the input data, rather the environment data (being the current datetime in this case).