|
-
My solution
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)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|