Help with SSIS expression

  • hello,

    I need to write something in SSIS that will say if my percentages does not equal 100 % then add the total, and whatever the difference is +/- i should use the biggest percentage and add the difference to that percentage to equal the whole to 100.

    example.

    all fields are in string/text in db.

    IDPERCENTTYPEPERCENT

    ABCDEFGH091011.42

    ABCDEFGH093511.54

    ABCDEFGH092013.36

    ABCDEFGH094513.74

    ABCDEFGH092513.78

    ABCDEFGH094017.46

    ABCDEFGH09552.85

    ABCDEFGH09503.5

    ABCDEFGH09155.02

    ABCDEFGH09307.3

    since the total of percent is < 100 (99.97), i need to add this difference (0.03) to type = 40.

    and, vice versa, if the total of percent is > 100, i need to subtract the difference from the biggest type to make it equal 100.

    can someone help?

  • ankita.patel01 81294 (12/27/2011)


    hello,

    I need to write something in SSIS that will say if my percentages does not equal 100 % then add the total, and whatever the difference is +/- i should use the biggest percentage and add the difference to that percentage to equal the whole to 100.

    example.

    all fields are in string/text in db.

    IDPERCENTTYPEPERCENT

    ABCDEFGH091011.42

    ABCDEFGH093511.54

    ABCDEFGH092013.36

    ABCDEFGH094513.74

    ABCDEFGH092513.78

    ABCDEFGH094017.46

    ABCDEFGH09552.85

    ABCDEFGH09503.5

    ABCDEFGH09155.02

    ABCDEFGH09307.3

    since the total of percent is < 100 (99.97), i need to add this difference (0.03) to type = 40.

    and, vice versa, if the total of percent is > 100, i need to subtract the difference from the biggest type to make it equal 100.

    can someone help?

    This looks like a straightforward T-SQL problem - can you explain why you want to use SSIS please?

    Also, can you explain why the PERCENT column is not saved in a numeric column?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • that is how we're receiving the data as text. and, need to use ssis to convert this data to proper data type and transform with these scenarios to load to another ods table every month.

  • ankita.patel01 81294 (12/27/2011)


    that is how we're receiving the data as text. and, need to use ssis to convert this data to proper data type and transform with these scenarios to load to another ods table every month.

    But you said

    all fields are in string/text in db.

    But I think that you're saying that the data arrives in a CSV file and that you are loading it into a database - is that correct?

    Please try to be clear with your requirements - or you'll end up receiving incorrect advice.

    What does 'ods' mean?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • yes, it is coming in as a csv file and being loaded to a table that has all varchar data types. i need to join this table with another and create a snapshot table with this transformation in it.

  • Hi,

    Please find the steps to handle the issue:-

    1. First you should a Dataconversion to get it in a proper format.

    2. Use the Aggregate Task to do the SUM for the Percent Column.

    3. Use the "Conditional Split Task" to check the condition if it >100 or < 100.

    4. After this use the Derived column in each split to get the difference.

    5. You can add or subtract that difference based on the requirement.

    I hopw this should handle your requirement. Please let me knoiw in case of any clarification

    Regards,

    Chandrashekhar

  • thanks chandrashekar. i have done that. i'm stuck on the derived column part. i have a conditional split to split into those records that are 100%, some that are errored and some that will need adjusting. I am doing max of the percent column and adding a derived variance column (100 - sum(percent)). I'm not sure how to tie the reccords to use this derived variance column in addition to all other percent columns coming in. can you advise?

  • I figured this out.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply