I am trying to build a process with SSIS to populate a History table for archive purposes. Basically we have a production table that is purged every day and only keeps the most recent 90 days. My task is to build a process that will run daily to merge the data from production to archive. This way reports can be generated against the archive table that allow for more than 90 days to be returned. I have been trying to work through using an OLE DB Data Source to connect to my staging table and return all records. I am then comparing those results using a Lookup to compare to the History Archive table. Then in to a Conditional Split to determine if the record is new and needs to be inserted in to the Archive or if the record has been changed in production and needs to be updated in the Archive. My issue is that the Lookup is not returning records in a way that I would expect and therefore I cannot use the Conditional Split to send the record to the appropriate output. I am trying to get some assistance to figure out if my process will even work of if I should be doing this a different way. If anyone can point me in a direction that will help me get the results I am looking for I would be greatful. This has to run in SQL 2005.

Hi, How do you purge the data every 90 days? using a SP, or using SSIS? If SP, It could be done using the OUTPUT or MERGE using T-SQL while you're running your purge SP

What do you mean by "My issue is that the Lookup is not returning records in a way that I would expect and therefore I cannot use the Conditional Split to send the record to the appropriate output." Is the lookup not finding what you expect? It sounds to me you are in the right path, although I would question the need of a conditional split. The lookup component, if configured correctly, should be able to differentiate existing records (to be updated) from new records (to be inserted). There is a thread in the FAQ at the top of the page you may find relevant. Look for: How do I check to see if a record exists in my destination and update it, and if it doesn't exist, insert it? Help Others! Don't forget to mark your thread as answered

Hi, How do you purge the data every 90 days? using a SP, or using SSIS? If SP, It could be done using the OUTPUT or regular T-SQL while you're running your purge SP.

What do you mean by "My issue is that the Lookup is not returning records in a way that I would expect and therefore I cannot use the Conditional Split to send the record to the appropriate output." Is the lookup not finding what you expect? It sounds to me you are in the right path, although I would question the need of a conditional split. The lookup component, if configured correctly, should be able to differentiate existing records (to be updated) from new records (to be inserted). There is a thread in the FAQ at the top of the page you may find relevant. Look for: How do I check to see if a record exists in my destination and update it, and if it doesn't exist, insert it? Help Others! Don't forget to mark your thread as answered