hi there, using SSIS i want to insert data if destination dont have else update it. my source is db2 and destination is SQL Server. below is my table and update SP create table tempjey(empid numeric(13,0) primary key, empname varchar(150)); this is my source and destination table structure -- ================================================ -- Template generated from Template Explorer using: -- Create Procedure (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the procedure. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= alter PROCEDURE dbo.uspUpdateTempjey (@inpEmpId Numeric(13,0 ), @inpEmpName varchar(150) ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here UPDATE TEMPJEY SET empname = @inpEmpName WHERE empid = @inpEmpId; END GO 1. i addded a OledbData source there i connect my db2 source database 2. then i added lookupdata, there i select the destination database, in the section of How to handle rows with no matching entries i have given as Redirect rows to no match output. so, it looks like this then 3. i added oledb destination under Lookup there i set the destination(SQL server) database and column mapped. there i Specify the error output to Redirect Rows. so it looks like below 4. then added Oledb Command under Lookup there i specified my update stored procedure like this dbo.uspUpdateTempjey ?,? on SqlCommand Property, column mappings looks like this EmpId -------@inpEmpId EmpName ------ @inpEmpname 5. at last i execute the program, first time i added 12 rows, it was fine then i changed empname on source so, i execute the same program again but now also none of the records go to OledbCommand instead all went to Oledb Destination so, my destination(MS-Sql) table waas not updated experts please guide me in right direction Thanks In Advance, Jeyaseelan

so, finally package seems below Thanks In Advance, Jeyaseelan

Hello, They were supposed to go to OLE DB Destination as you are using two columns in lookup (Empid,empname), if any of them does not match with destination records then they are supposed to go to Lookup No Match. As you mentioned you have changed "empname" , that make them different what you have in destination and that's why they went to OLE DB Destination. If you will use only Empid then your new records will be only if no empid exits in destination table... Not sure what is your Key empid, or empname or both. Thanks http://sqlage.blogspot.com/

amir thanks for your response. i knew some where i did mistake thats what i posted with images so, could you tell me the exact place of flaw?Thanks In Advance, Jeyaseelan

How you will say this is new record? i)If empid exists in source and not in destination ii) or if combination of empid+empname exists in source and not in destination? As from lookup , you are following second step and your process is working correctly. if you need to check the new record according to empid only then please map empid in lookup. you don't need empname there. You will do the update by using your stored procedure in OLEDB Command by using empid as parameter , not both of them. I am sorry if i misunderstood your question.Please explain little more what you are trying to achieve. Thankshttp://sqlage.blogspot.com/

Aamir thanks for your response. so, as per your idea i changed the look like this eventhough nothing is moving to update. do you think did i anything wrongly?Thanks In Advance, Jeyaseelan

experts just now i got a small idea, that is instead of numeric(13,0) on source and destionation ID columns i used Int data type now i can see insert and update properly. but now on my source i cant change the data type to Int so, please tell me how to convert the column before lookup?Thanks In Advance, Jeyaseelan

i found the solution on here http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/fdaa6b69-c6ff-4cfd-99c6-ccfc61890c19Thanks In Advance, Jeyaseelan