Building History Tables and not have any luck

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.

I have attached a screen shot of the current data flow task.

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.

gbargsley,

Unfortunately your screenshot did not show up.  

Pragamatic Works Task Factory has an excellent Upsert component that makes this job so much easier.  If you had SQL 2008 you could use the SQL MERGE statement.  You could still do an upsert the old fashioned way.

I pulled an example from this site.

--Creating the tables and insert some test data
create table tbl (i int not null primary key, c char(5))
go

insert into tbl (i,c)
select 1, 'abc'
union 
select 2, 'def'
union 
select 3, 'ghi'
go

create table StagingTbl (i int not null primary key, c char(5))
go


insert into StagingTbl (i,c)
select 3, 'ghijk'
union 
select 4, 'lmnop'
go

--First step is to update column c according to i
--I do it with an update statement that uses
--from clause.  You can read about it in BOL
update tbl
set tbl.c = StagingTbl.c
from tbl inner join StagingTbl on tbl.i = StagingTbl.i

--The insert is done with select that is doing a left
--join and inserts only records that were not found
--in tbl1.
insert into tbl (i,c)
select StagingTbl.i, StagingTbl.c
from StagingTbl left join tbl on StagingTbl.i = tbl.i
where tbl.i is null
go

--Check the results
select * from Tbl

--cleanup
drop table tbl
go
drop table StagingTbl

To do the same in SSIS, this link provides two methods: SSIS – Performing An UPSERT

One more:  Easy Upserts in SSIS 2005

If you still have specific questions, post back and please email your screenshot to me directly.

Thanks,

Mike