SSIS HELP!

  • with "Target Field (Address1 varchar(30) & Address2 varchar(30)"

    My question is " I don't want truncate data if the data is more than 30 char. If the Mailing_Address is less than 30 char stay in Address1 & if the data is more than 30 char rest of the data after 30 char transfer to Address2 field. "

    Note:- How i can accomplish this one in SSIS. I believe i can use "Derived Transformation" or "Conditional Split" I need your help about "Expression". If my question is not clear Please let me know I will try to explain one more time. Thanks in advance.

  • rocky_498 (11/28/2010)


    with "Target Field (Address1 varchar(30) & Address2 varchar(30)"

    My question is " I don't want truncate data if the data is more than 30 char. If the Mailing_Address is less than 30 char stay in Address1 & if the data is more than 30 char rest of the data after 30 char transfer to Address2 field. "

    Note:- How i can accomplish this one in SSIS. I believe i can use "Derived Transformation" or "Conditional Split" I need your help about "Expression". If my question is not clear Please let me know I will try to explain one more time. Thanks in advance.

    Perhaps you would want to increase the size of your target column.

    Your purpose cannot be solved by SSIS.

    Raunak J

  • I disagree. It seems relatively straightforward to me.

    You need two derived columns - Add1 and Add2 say.

    Add1 will be Left(Address, 30)

    and Add2 will be characters 31-60 (use a substring function to get that - haven't got time to check syntax at the moment).

    Post back if you need help with the syntax - but please have a go first, as it's not very difficult.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil Parkin (11/29/2010)


    I disagree. It seems relatively straightforward to me.

    You need two derived columns - Add1 and Add2 say.

    Add1 will be Left(Address, 30)

    and Add2 will be characters 31-60 (use a substring function to get that - haven't got time to check syntax at the moment).

    Post back if you need help with the syntax - but please have a go first, as it's not very difficult.

    Add2 = SUBSTRING(Address,31,60)

    Raunak J

  • As suggested by Phil, Raunak you can do. Also you can do this using script task. Some sample code below.

    Dim address1, address2 As String

    address1 = Dts.Variables("v_Address1").Value.ToString ' From Variable

    If address1.Length() < 30 Then

    Dts.Variables("v_Address1").Value = address1

    Else

    Dts.Variables("v_Address1").Value = address1.Substring(0, 29)

    Dts.Variables("v_Address2").Value = address1.Remove(0, 29)

    End If

    Dts.TaskResult = Dts.Results.Success

  • Sorry for late reply, Thanks all of you guys your help. I can't change my target table.

    I really appreciate if some 1 send me " Expression", Thanks.

  • rocky_498 (11/30/2010)


    Sorry for late reply, Thanks all of you guys your help. I can't change my target table.

    I really appreciate if some 1 send me " Expression", Thanks.

    the solution is already given above.

    in the derived column transformation task you need to have Two new columns addres1 and address 2 with the expressions

    SUBSTRING(Mailing_Address ,1,30)

    SUBSTRING(Mailing_Address ,31,60)

  • Thank you, I was not clear):,

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

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