A SSIS package

Hi,

I am used Visual Studio SSIS wizard to transfer some data from one database to another with the same table structure. This is the first time I use SSIS. I see two objects created. OLE DB Source extracts some data based on the create date and OLE DB Destination object is a corresponding table.

So query in OLE DB Source:

Select ID,[Desc],[CreateDate] from TableSrc where ],[CreateDate] between '1/1/2006' and '1/31/2006'

OLE DB Destination has TableDest as the destination. TableDest has the same structure as TableSrc.

My problem is that when I run the package twice the data will be imported twice. I need to use this package for both new records and updated records in TableSrc . Is there any way I can check if the ID is available in the TableDest, I perform update otherwise perform insert into TableDest.

Thanks,

I can't find a simple way to do this in SSIS. I'd better do this in T-SQL, usingLinked Servers. Suppose you've set a linked server (namedSourceServer) for the source server on destination server, you can use such query to accomplish INSERT/UPDATE:

UPDATE TableDest
SET [Desc]=src.[Desc], [CreateDate]=src.[CreateDate]
FROM TableDest dest JOIN [SourceServer].SourceDB..TableSrc src
ON dest.ID=src.ID
WHERE?src.[CreateDate] between '1/1/2006' and '1/31/2006'

INSERT INTO TableDest
SELECT * FROM [SourceServer].SourceDB.TableSrc src
WHERE?src.[CreateDate] between '1/1/2006' and '1/31/2006'
AND src.ID NOT IN (SELECT ID FROM TableDest)