Compare Data Before Inserting

I have a DTS package in which I would like to move to SSIS and also add some new functionality. My current DTS package is pretty simple:

1. Download a flat file from an FTP site
2. Execute a SQL task that deletes the data from the table
3. Insert the data into the table.

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes this can be done in SSIS, but the real questions are what identifies the data as "new" and how many records are you planning to process at one time.
If you have a primary key in the source data and you are working with few records < 50k, then you could perform a lookup on the destination to see if the primary key exists. If it does not then you could redirect the row to an insert data flow using the error output. If it does exist (std output), you could (in the typical case) direct it to an update data flow.
If you have a primary key and are working with many records, you could perform a merge join on the source and destination and then use a conditional split to filter out/redirect the rows where the destination key is null.
If you have some other identying attributes, then the methodology may change. You may even want to investigate the Slowly Changing Dimension transform.
Larry
|||

Jason92 wrote:

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes. Check out: http://www.sqlis.com/default.aspx?311

-Jamie