Hi Guys, I needed to compare the quantity breakdown (Table1) to the summarize quantity (Table2) of a TypeCode = "MC". Table1 is in the dataflow. ParentID ID Desc RowType TypeCode Group Qty 1 1 VM RYC ITEM M NULL 1 1 2 VM COKE ITEM MC DRINK 1 1 3 VM FRIES ITEM MC FRIES 1 10 RICE ITEM S NULL 1 4 4 VM CHCKN ITEM M NULL 2 4 5 VM COKE ITEM MC DRINK 1 4 6 VM FRIES ITEM MC FRIES 2 4 7 VM SPRITE ITEM MC DRINK 1 Table2 is in the datatable ParentID Group Qty 1 DRINK 1 1 FRIES 1 4 DRINK 2 4 FRIES 2 I needed to verify if the Qty by ParentID of the Table1 are correct to the number of Total Qty of Table2 using ParentID and Group columns as a relationship, and if there is any inconsistencies found, the package will fail. Anybody know how to do that? or if you guys have another method to do this i am very glad to learn. Thanks,

you can use an execute sql task and write this query in sqlstatement: select count(*) as cnt from table1 inner join table2 on table1.ParentID=table2.ParentID and table1.Group=table2.Group where table1.Qty<>table2.Qty then set result set of execute sql task to single row, define an Int32 package variable , name it as InconsistenceCount, and set it in execute sql task editor, result set tab, set variable as InconsistenceCount and set result name as cnt. then you can check the value of InconsistenceCount variable, if this is 1 or more, this means that you have inconsistence and you can redirect to a task which fail the package . if value was 0, everything is OK, and you can redirect to next task. let me know where you need more details. http://www.rad.pasfu.com

Hi Raad, I was thinking doing this in a Script Component because the Table1 data are inside of the data flow. Thanks,

I recommend to do this outside of data flow task, because in the data flow the execution of stored procedure in an oledb command, or an script component will do ON EVERY ROW. But your requirement can be done all in one script without applying on each row. do you fetch table1 data from same database which table2 comes from?http://www.rad.pasfu.com

I see your point, with regards to your question, my answer is no. table1 data is from a Flat file Source that i did some transformations. hence table2 is from a database that i stored inside of the data table using script component in hope i can accomplish my requirements.

OK, Fair enough . So, in this case you can use data flow task Lookup transform, set table2 as lookup table. and connect flat file source(table1) to input of lookup transform. you can set "rows with no match" as redirect to no match output , or leave it as fail component ( this is default). http://www.rad.pasfu.com

I don't think Lookup Transform can accomplish my requirement. Table2 Qty is the aggregated value of the Qty of Table1 per ParentID. If I do a lookup transform, how do i know if Table1 Qty is correct per ParentID?

Do you mean that you should calculate sum of Qty group by ParentID in table1? if yes, you can use aggregate transformation after flat file source, and then connect output of aggregate transform to the lookup input. http://www.rad.pasfu.com

If I use Aggregate Transform, the Output is already aggregated. But I need to maintain the original Table1 data structure (the same as above)after the checking so that i can load it into a destination.

use a MultiCast transform right after flat file source, this will generate copies of your data stream, then connect one copy to aggregate transform, and connect another copy to destination.http://www.rad.pasfu.com