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,

That is a verry common scenario when loading data. The most popular solution in this forum is tu used a Lookup transform in the data flow against the destination table; no matches in LU transform are treated as errors; so you can configure the error output of the LU transform to 'redirect error'; then the error output is your 'new rows' out put and the no-error output is the 'existing rows' output.:

Somewhere in this thread there is a link to Jamie's blog where that technique is explanied (an other options discussed).

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=779836&SiteID=17

Be sure to understand how Lookup transform uses memory resources as that could play against you if the volume of data is to big or if memory not enough

|||I second Rafael's comments.