Hi, i had main table like following Id Department_id Amount 1 2 20 2 2 70 3 1 80 other table is like following Department_ID Limit 1 50 2 80 3 50 how i can achieve following result in ssis for using SQL Id Department_id Amount inlimit outlimit 1 2 20 20 0 -- this is because this department has limit 80 in above table 2 2 70 60 10 -- now in above record we use 20 out of 80, limit remain is 60 that why 70 split in 60/10 3 1 80 50 30-- this department has limit 50 what why 80 is split into 50/30 now for any new in -- department 3 will go in outlimit as all limit is utilized Basically i after every match look we need to reset the limit in limit table for next record. Any idea best way to achieve above.

I see two ways: 1. simple, very slow: instead of using lookup use OLE DB Command Transformation to update 2hd table. Use variables in update to return inlimit and outlimit. 2. use script component: load 2nd table in .NET dictionary structure and use .NET code to update this dictionary during the flow and calculate inlimit and outlimit.

Hi Mujahid, I think the best way would be to create a stored procedure and calculating the result in a temp table and returning temp table. This procedure will be efficient only if there is not much data(Millions) in the tables. If the first table is MainDepartmentTable and secod table is DepartmentLimit, then The following code will calculate the result in #tempResultDepartment Forgive me if there are syntax issue. I had written code in notepad without SQL server intellisence create table #tempResultDepartment(Id int, Department_id int, Amount int, inlimit int,outlimit int) DECLARE @intFlag INT SET @intFlag = 1 DECLARE @TotalCount INT SET @TotalCount = SELECT MAX(ID) FROM MainDepartmentTable DECLARE @tempId Int Declare @tempDepartmentID Int Declare @tempAmount Int Decalre @tempInlimit Int Declare @tempOutlimit Ind WHILE (@intFlag <=@TotalCount) BEGIN SELECT @tempId=Id,@tempDepartmentId=Department_Id,@tempAmount=Amount from MainDepartmentTable where Id=@intFlag Set @Intlimit=(SELECT TOP 1 LIMIT from DepartmentLimit where Id=@tempId)-(SELECT SUM(inlimit) from #tempResultDepartment where Department_id=@tempDepartmentId)-@tempAmount SET @Outlimit=-(SELECT TOP 1 LIMIT from DepartmentLimit where Id=@tempId)-(SELECT MAX(outlimit) from #tempResultDepartment where Department_id=@tempDepartmentId) IF(@Outlimit>0) BEGIN SET @Intlimit=0 SET @Outlimit=(SELECT MAX(outlimit) from #tempResultDepartment where Department_id=@tempDepartmentId) + @tempAmount ELSE Set @Intlimit=(SELECT TOP 1 LIMIT from DepartmentLimit where Department_Id=@tempDepartmentId)-(SELECT SUM(inlimit) from #tempResultDepartment where Department_id=@tempDepartmentId)-@tempAmount IF(@INTlimnit<0) SET @OUTLIMIT=-(INTlimit) SET @INTlimit=(SELECT TOP 1 LIMIT from DepartmentLimit where Department_Id=@tempDepartmentId)-OUTLIMIT ELSE @OUTLIMIT=0 END END Insert into #tempResultDepartment(Id,Department_id,Amount,inlimit,outlimit) Values(@tempId,@tempDepartmentId.,@tempAmount,@INTLimit,@OUTimit) Set @intFlag=@intFlag+1 Thank you

Piotr's second idea holds the most merit to me. The data stays in the pipeline, and uses very fast Hashtable lookups and calculations to push the data through. Create a class to hold a row's worth of your lookup table - including the "current" value. Cache your lookup table in the PreExecute override into a Hashtable of instances of that class, then reference (and modify) that Hashtable in ProcessInputRow. Talk to me now on