Post reply

Group By and Maximum of a String

  • January 6, 2008 at 9:40 pm

    #185249

    Hi all

    In the middle of a SSIS Data Flow task I have the following four columns:

    ·Opportunity_Job_Number (int)

    ·Opportunity_Job_Suffix (char(2))

    ·Converted_Job_Number (int)

    ·Converted_Job_Suffix (char(2))

    I want a Data Transformation Task to transform the data in the pipeline such that there is one row per Opportunity_Job_Number / Opportunity_Job_Suffix combination. Currently this part of the pipleline has multiple rows per Opportunity_Job_Number / Opportunity_Job_Suffix combination. For each Opportunity_Job_Number / Opportunity_Job_Suffix combination I want the lowest Converted_Job_Number and the highest Converted_Job_Suffix.

    I can use the Aggregate transform to Group by Opportunity_Job_Number and Opportunity_Job_Suffix and to get the minimum of Converted_Job_Number. But the Aggregate transform does not allow me to get the maximum of Converted_Job_Number as this column is a string and the Aggregate transform does not allow maximum on a string.

    I could use the Script transform … but I’m not a Visual Studio .Net coder.

    Is there a way of achieving this without having to use the Script transform?

    Regards

  • January 7, 2008 at 3:43 am

    #765270

    Can U write a sql query in Destination using SQL Command.Something like

    Select xyz from mn where xyz in(Select Top() from opp) order by desc.This will give you the smallest or largest value on Top based on order by.

    The other way could be convert the string column to int it it contains int data only using Data conversion task.

    Cheers

    Sid

  • January 7, 2008 at 10:30 pm

    #765572

    Thanks Sid.

    The transform where I require the maximum of the string is connected to a merge join and then further transforms are performed. Using a destination is not appropriate.

    I’m unable to convert the contents of the string Converted_Job_Suffix to an integer as it contains character values.

    In the end I’ve used the Script transform … but if someone knows of a better way then please post it.

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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