I've just created my first SSIS package. Right now it just consists of a Data Flow task which has inside of it a OLEDB Source task with my sql query, and a Flat File destination which outputs one column of the data to a flat file. Back up on the Control Flow level I also have a File System task which moves the flat file to another location.
What has me stumped is how I achieve my desired next steps. Basically I need to build in the ability to remove any duplicates (items that have already been put into the flat file) before the flat file is created. I have a plan, I just don't know how to get there. Here is what I'm thinking: Initially, just on the first run, run two separate but identical OLEDB sources, one to create my flat file and one to go to a separate flat file that I'll use as my 'archive" and input for the duplicate check. As far as the actual duplicate check, I have an example I can follow for this so I'm not too concerned there. Once the duplicate check is complete I'm hoping to be able to append any new rows to the end of this "archive" flat file which I'm using as input for the duplicate check.
So, I guess my question is - does this approach make sense? And also, will it be possible to append rows to the archive file as I am hoping?