Hi Gurus, I have a requirement to calculate "Result" column as below: Here, we are multiplying by taking the Item# by an integer. lets take ex: for (1,1.1,1.2) the qty is 5 for Item# 1. So, for each qty in like 1.1,1.2 we multiply by 5 so we get 10,5 resp. in the result column. How can I do this in SSIS . Please help me with this. Item# qty Result 1 5 5 1.1 2 10 1.2 1 5 2 3 3 2.1 1 3 2.2 2 6 2.3 2 6

Hi, You can use Derived Column transformation to accomplish this. In the derived column, just multiply the Item# with Qty and you should be all set. An example of Derived Column transformation - http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspxShahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

Hi, You can use Derived Column transformation to accomplish this. In the derived column, just multiply the Item# with Qty and you should be all set. An example of Derived Column transformation - http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspx Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com I understand that I could use der. col but my requirement is not a straight multiplication. Please see my req. above.if it is not clear let me know.

If you want to multiply values from different records you need a different approach. 1) You could split the 1 and 2 rows from the 1.1 1.2 2.1 (etc) rows with a conditionals split and merge join them so that the values for the calculation are in the same row. 2) Use a Script Component to remember values from row 1 and use them if row 1.1 and 1.2 passes the Script Component. Here is a row compare example: http://microsoft-ssis.blogspot.com/2011/04/compare-values-of-two-rows.htmlPlease mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

If you want to multiply values from different records you need a different approach. 1) You could split the 1 and 2 rows from the 1.1 1.2 2.1 (etc) rows with a conditionals split and merge join them so that the values for the calculation are in the same row. 2) Use a Script Component to remember values from row 1 and use them if row 1.1 and 1.2 passes the Script Component. Here is a row compare example: http://microsoft-ssis.blogspot.com/2011/04/compare-values-of-two-rows.html Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter Hi SSISJoost, I am not good with script. If I chose to go with 1). then what do I do after having cond split. As I understand I get two data set one with 1.1,1.2 and other with 1,2,3, etc.. . But after that do I how do I calculate like above my req?

Spliting will result in two sets SET 1 Item# qty 1 5 2 3 SET 2 Item# qty 1.1 2 1.2 1 2.1 1 2.2 2 2.3 2 And you have to join the two sets on something... so create an extra column (Derived Column) in SET 2 (with 1.1, 1.2, 2.1, etc.) that only contains the 1 and the 2. That could be done with a substring and a findstring. Now you can sort both sets on the Item-column (SET 1) / newly created column (SET 2) with a Sort Transformation. And now they are sorted, you can join them (Merge Join) After the merge join you can add an other derived column with result formula. result: Item# qty Item# qty Result 1 5 1.1 2 10 1 5 1.2 1 5 2 3 2.1 1 3 2 3 2.2 2 6 2 3 2.3 2 6 Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Spliting will result in two sets SET 1 Item# qty 1 5 2 3 SET 2 Item# qty 1.1 2 1.2 1 2.1 1 2.2 2 2.3 2 And you have to join the two sets on something... so create an extra column (Derived Column) in SET 2 (with 1.1, 1.2, 2.1, etc.) that only contains the 1 and the 2. That could be done with a substring and a findstring. Now you can sort both sets on the Item-column (SET 1) / newly created column (SET 2) with a Sort Transformation. And now they are sorted, you can join them (Merge Join) After the merge join you can add an other derived column with result formula. result: Item# qty Item# qty Result 1 5 1.1 2 10 1 5 1.2 1 5 2 3 2.1 1 3 2 3 2.2 2 6 2 3 2.3 2 6 Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter At the merge join step: What kind of JOIN should I choose? When used inner join, and I chose Item from set2 I am getting only set2 values and missing other set1 values Also, Can I get in the same format I mentioned above?

inner join souds good. Make sure both sets are filled and sorted. what did you do with the conditional split? Whats the expression you're using?Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

inner join souds good. Make sure both sets are filled and sorted. what did you do with the conditional split? Whats the expression you're using? Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter These are the steps I have DF.The exp I am using is : SUBSTRING([Item#],FINDSTRING([Item#],".",1) + 1,3) 1. First I have my excel source 2. conditional split 3a. Integer Item# 3b. Float Item#(1.1,1.2 ...) 4a. Now under 3a. I have sort by Item(I did not pass any other col here) 4b. Now under 3b. I have der col(rslt) to get values after decimals as you mentioned.under that I have sotrby der col (rslt)(Here I passed all my other columns) 4. Then I have innner join step on Item# and der col (rslt) 5. One der transf. to calculate RESULT= qty(from step 4b )*rslt(from above der. col step4b) To get the result I am expecting what are the steps I am missing . Thanks sparta

The calculation will work only when the joining column in both sets is the same. If you are joining on Item# they should have the same value. I would suggest cast the Item# to integer to start with. This will convert all 1.1, 1.2 to 1 and 2.1,2.2,2.3 to 2. Then simply join on this integer column and it should work fine.~V

Should look something like this: Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

or so: Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

or so: Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter Hi SSISJoost, I did as you suggested in the last screenshot.I just added row sampling to get the o/p view. But calculations I got is different than I expected. below is the screenshot after calculating final [RESULT=join col* qty] column: But my RESULT in my qn: above are different numbers.Can you guide me what I am doing wrong here?

are you multiplying the right columns? [qty] * [qty (1)] ps rename the columns in the merge join... that's clearer. Otherwise you get names with " (1)" at the end. and you don't need the temporary join column... remove all unnecessary columns in the merge join for better performance. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

are you multiplying the right columns? [qty] * [qty (1)] Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter I am calculating as Join col* qty

are you multiplying the right columns? [qty] * [qty (1)] Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter I am calculating as Join col* qty that's wrong... add an extra derived column after creating the join column that replaces the qty by 1 if the item is 1 or 2 or 3 or etc. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

are you multiplying the right columns? [qty] * [qty (1)] Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter I am calculating as Join col* qty that's wrong... add an extra derived column after creating the join column that replaces the qty by 1 if the item is 1 or 2 or 3 or etc. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter Hi SSISJoost, It worked with dummy data. But when I am using this with my real data. I am getting the error message at the cond. split task: The data types "DT_R8" and "DT_WSTR" are incompatible for binary operator "==". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator. [Conditional Split 1 [2071]]: Attempt to set the result type of binary operation "(DT_R8)(DT_I4)[Item No] == [Item No]" failed with error code 0xC0047080. Hiow can I solve this?

Can't really help solving crap data in excel... excel is a pain in the *** There are probably different data types in the excel column (strings and numerics) http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.html Check if the column datatypes in SSIS are different with the dummy excel and the real excel.Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Can't really help solving crap data in excel... excel is a pain in the *** There are probably different data types in the excel column (strings and numerics) http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.html Check if the column datatypes in SSIS are different with the dummy excel and the real excel. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter Thank you for your help SSISJoost. I finally was able to figure it out with your help.

Can't really help solving crap data in excel... excel is a pain in the *** There are probably different data types in the excel column (strings and numerics) http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.html Check if the column datatypes in SSIS are different with the dummy excel and the real excel. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter Here is the new link to my latest thread: