Hi, I have a text file in the following format, CustId,CustName 1,David 2,Mathew 3,Joseph 4,Amy 5,Mary 6,Sarah the business rule is like the 1st record belongs to America,the 2nd record belongs to uk and the 3rd record belongs to Europe.The pattern continues for the remaining records again like 4th record belongs to America,the 5th records belongs to uk and the 6th records belongs to Europe and so on Now we have to partition the records based on the country,and load them into three different tables. Help is always appreciated. Regards Raja

You can achieve this by doing conditional split transformation... here is more help http://www.mssqltips.com/tip.asp?tip=2047 http://uk.linkedin.com/in/ramjaddu

Is there anything is the data itself that dictates which County the row belongs to, or is it just a repeating cyclical pattern of those three choices?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hi, The best approach is always to get the source to say which country the customer belongs to. But if that's the 'business rule', then I would suggest using a "Derived Column" component in the Data Flow and created a column (e.g. CustCountry) using the following expression: CustId % 3 == 1 ? "America" : CustId % 3 == 2 ? "UK" : "Europe" Then you can use a "Conditional Split" component to separate the flow into three different destination component. This will only work on the assumption that there is no skipping of CustId. (ie CustId is always 1,2,3,4,5,6... not 1,2,4,5,7) Though I still would like to emphasize that this approach is not ideal and has its limitation and you may need to modify your package later if there's a change of 'business rule', e.g. 4th record belongs to Asia, etc. Hope this helps. J.

There is nothing as such in data that dictates which country it belongs to,its just a cyclic order...even the customerids are not in sequqnce.....

Use the following query in your source component: SELECT CustId, CustName, 'RowNumber' = ROW_NUMBER() OVER(ORDER BY CustId) FROM Table_1 Then add a conditional split to your package, and redirect rows into diferrent outputs based on the value in RomNumber column: Output 1 -> RowNumber % 3 == 1 Output 2 -> RowNumber % 3 == 2 Output 3 -> RowNumber % 3 == 3 Then connect each output of conditional split to the appropriate destination. Cheers! Please mark as answer if this helps. Thank you. http://thebipalace.wordpress.com

If your source doesn't support SQL, then you'll probably need to use a Row Numbering transform or script. Talk to me now on

Hi, This: Output 3 -> RowNumber % 3 == 3 will not give any output row as a mod 3 will never give you a 3. Instead, try this: Output 3 -> RowNumber % 3 == 0 Hope this helps. ~ J.

Hi, This: Output 3 -> RowNumber % 3 == 3 will not give any output row as a mod 3 will never give you a 3. Instead, try this: Output 3 -> RowNumber % 3 == 0 Hope this helps. ~ J.

Hi, This: Output 3 -> RowNumber % 3 == 3 will not give any output row as a mod 3 will never give you a 3. Instead, try this: Output 3 -> RowNumber % 3 == 0 Hope this helps. ~ J. Thanks Jona for correcting me. I wrote it so quickly and carelessly. :-)http://thebipalace.wordpress.com

Hi, This: Output 3 -> RowNumber % 3 == 3 will not give any output row as a mod 3 will never give you a 3. Instead, try this: Output 3 -> RowNumber % 3 == 0 Hope this helps. ~ J. Thanks Jona for correcting me. I wrote it so quickly and carelessly. :-)http://thebipalace.wordpress.com

1. Add a script transform after your source 2. add an ID as output name it as ID2 3. in script transform declare a static variable id2 in Input0_ProcessInputRow method add Row.ID2 = id2 % 3; id2 = id2 + 1; now use a conditional split to split data Spandan

1. Add a script transform after your source 2. add an ID as output name it as ID2 3. in script transform declare a static variable id2 in Input0_ProcessInputRow method add Row.ID2 = id2 % 3; id2 = id2 + 1; now use a conditional split to split data Spandan