I created an SSIS Pacakge that imports a monthly extract claims file. I have a simple Data Flow task, flat file to OLEDB Destination with a Derived column component in between used to generate a primary Key (based on a combination of 3 columns in the extract file).
What I would like to do if possible is have the SSIS package only import brand new records based on the primary key so that if there is a file that is being imported that contains a (generated) primary key that already exists in the destination table, then I would like the package to delete the existing record in the table and then add the new record from the current file being imported.
I know I could do this by having a staging table where I load the file and then using some sql queries (sp) to delete and add records to the table appropriately but I am trying to accomplish the import without needing to create a staging table because the table that I am currently populating, already is a staging table...
Is what I am trying to accomplish possible, using SSIS and only 1 destination table or will I need to create an additional staging table?