none
Derived Column Expression SSIS

    Question

  • Other-Data

    u1=EUR;u2=sss;u3=5547;u4=8966

    I need to create new derived columns from the string above. The output needs of the new derived columns needs to take the value after = as follows:

    U1 U2 U3 u4
    EUR sss 5547 8966

    but I'm struggling with the expression required to achieve so. Can anyone help?

    Thanks,

    R

    Saturday, October 20, 2012 1:44 PM

Answers

  • You need to use combination of FindString() & Substring Function.

    U1 = Substring([Others-Data],Findstring([Other-Data],"u1=",1)+3,(Findstring([Other-Data],";",1) - (Findstring([Other-Data],"u1=",1)+3)) 
    
    --untested

    BOL Says "FINDSTRING works only with the DT_WSTR data type. character_expression and searchstring arguments that are string literals or data columns with the DT_STR data type are implicitly cast to the DT_WSTR data type before FINDSTRING performs its operation. Other data types must be explicitly cast to the DT_WSTR data type. "

    Refer this.

    http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/a208f352-1044-4146-b58f-b23d479b9e21


    Rajkumar



    • Edited by Rajkumar5055 Saturday, October 20, 2012 2:25 PM
    • Marked as answer by Ertiti Saturday, October 20, 2012 4:45 PM
    Saturday, October 20, 2012 1:52 PM
  • See My first post, I have given an expression to get U1. Let us know your observations.

    Which version of SSIS you using? If 2012, Its very easy with TOKEN() Function. Refer This

    http://msdn.microsoft.com/en-us/library/hh213216.aspx

    In the following example, the TOKEN function returns "dog". The delimiter string in this example contains 5 delimiters. The input string contains 4 tokens: “a”, “little”, “white”, “dog”.

    TOKEN("a:little|white dog","| ,.:",4)
    


    Rajkumar


    • Edited by Rajkumar5055 Saturday, October 20, 2012 2:51 PM
    • Marked as answer by Ertiti Saturday, October 20, 2012 4:45 PM
    Saturday, October 20, 2012 2:32 PM

All replies

  • You need to use combination of FindString() & Substring Function.

    U1 = Substring([Others-Data],Findstring([Other-Data],"u1=",1)+3,(Findstring([Other-Data],";",1) - (Findstring([Other-Data],"u1=",1)+3)) 
    
    --untested

    BOL Says "FINDSTRING works only with the DT_WSTR data type. character_expression and searchstring arguments that are string literals or data columns with the DT_STR data type are implicitly cast to the DT_WSTR data type before FINDSTRING performs its operation. Other data types must be explicitly cast to the DT_WSTR data type. "

    Refer this.

    http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/a208f352-1044-4146-b58f-b23d479b9e21


    Rajkumar



    • Edited by Rajkumar5055 Saturday, October 20, 2012 2:25 PM
    • Marked as answer by Ertiti Saturday, October 20, 2012 4:45 PM
    Saturday, October 20, 2012 1:52 PM
  • Thanks Rajkumar. 

    I've tried SUBSTRING([Other-Data], 1, FindString([Other-Data],"u3="),1 but I'm obviously missing something, because it does not work. 

    Any ideas?

    Ta,

    Ertiti


    • Edited by Ertiti Saturday, October 20, 2012 2:26 PM
    Saturday, October 20, 2012 2:26 PM
  • See My first post, I have given an expression to get U1. Let us know your observations.

    Which version of SSIS you using? If 2012, Its very easy with TOKEN() Function. Refer This

    http://msdn.microsoft.com/en-us/library/hh213216.aspx

    In the following example, the TOKEN function returns "dog". The delimiter string in this example contains 5 delimiters. The input string contains 4 tokens: “a”, “little”, “white”, “dog”.

    TOKEN("a:little|white dog","| ,.:",4)
    


    Rajkumar


    • Edited by Rajkumar5055 Saturday, October 20, 2012 2:51 PM
    • Marked as answer by Ertiti Saturday, October 20, 2012 4:45 PM
    Saturday, October 20, 2012 2:32 PM
  • I'm on SQL Server 2008R2, unfortunately...

    your script SUBSTRING([Other-Data],FINDSTRING([Other-Data],"u1=",1) + 3,FINDSTRING([Other-Data],";",1) - (FINDSTRING([Other-Data],"u1=",1) + 3)) works if there is order in the dataset. 

    I should have provided an actual example that illustrates the challenge at hand - please see below. I'd need to parse the string and select the different values from the variables (e.g. u1, u2, u3, etc.) for the specific columns. 

    Other-Data
    u3=odw : CH : de : hom;u2=odw : Product : DSC-HX20V;~oref=http://www.bidl.ch/lang/de/product/dsc-h-series/dsc-hx20v
    u1=EUR;u2=sss:Checkout-Step4:Orderacknowledgement;u3=DE:de:hom;u11=1;u12=302338533;u13=SVE1511C5E;u14=575.67;~oref=https://shop.bidl.de/shop/bibit/success.do
    u15=1187;u13=SVE14A1C5E~VAIOEWY401;u11=1~1;u10=843.9~121.14;u9=1038~149;u3=crm : FI : fi : hom;u1=EUR;u2=crm : Checkout : Order acknowledgement;~oref=https://shop.bidl.fi/shop/bibit/success.do

    Saturday, October 20, 2012 4:16 PM
  • Hello,

    Do you expect fixed number of columns from each text?

    Each line from other-data has different combination of columns.


    Rajkumar

    Sunday, October 21, 2012 1:17 PM
  •  Hi,

    All the possible values will be U1 all the the way to U20, though not all of them will be present all the time across all records. The file has 30 million rows, and each line from other-data will have difference combinations....

    Thanks,

    Tuesday, October 23, 2012 9:13 AM
  • Hi,

    If that is the case I would suggest you to create a script component and make use of VB.Net or C#(If SSIS 2008 and above).

    It doesn't look all that simple if you are expecting 20 columns.


    Rajkumar

    Tuesday, October 23, 2012 9:25 AM
  • I got the answer in another forum. Attached below in case anybody needs it...

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
            '
            Dim Value As String
            Dim Pointer As Integer
            Dim Item As String
            Dim Data As String
            Dim column As String

            Data = Row.data
            While Len(Data) > 0
                Pointer = InStr(Data, ";")
                If Pointer > 0 Then
                    Item = Left(Data, Pointer - 1)
                    Data = Mid(Data, Pointer + 1)
                Else
                    Item = Data
                    Data = ""
                End If
                column = Left(Item, 3)
                Value = Mid(Item, 4)

                Select Case column
                    Case "u1="
                        Row.U1 = Value
                    Case "u2="
                        Row.U2 = Value
                    Case "u3="
                        Row.U3 = Value
                End Select

            End While

            '
        End Sub

    Sunday, October 28, 2012 10:36 PM