SSIS How to split a single string into multiple columns using Derived Column

  • Hello,

    In my source Column having comma separated with text qualifier(“), I would need derived column from source column like below. Please suggest the best SSIS Expression to archive this .

    Note : I am trying to derived 50+ Columns and using the string TOKEN(REPLACE(SourceRecord,"\"",""),",",1), it wont work if the Column value has comma.

    Annotation1

    Annotation2

    Thanks

  • if your source is a file then just split it on the file definition by setting up the file with comma delimited as column separator, quoted

  • This can be done, if you think laterally: make " your delimiter and your formulas become:

    TOKEN( SourceRecord,"\"",1)
    TOKEN( SourceRecord,"\"",3)
    TOKEN( SourceRecord,"\"",5)
    TOKEN( SourceRecord,"\"",7)
    TOKEN( SourceRecord,"\"",9)

    Having said that, Frederico's suggestion is better, if applicable.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Thanks you Phill !!

    Your solution is working as expected, however any of the column value has empty, I did not get the proper value in corresponding output columns.

    Source Column/ OutPut Coulumn:

    Annotation4

     

     

    • This reply was modified 7 months, 3 weeks ago by  sabarishbabu.
  • My solution depends on there being the same number of quotation marks (") in every string which is split.

    If an 'empty' column appears as "", the solution should still work. But if the quotation marks are not there, it will not.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Thanks for your quick reply !! Yes the  quotation marks are there in the empty field too, even its not working for if any empty column value.

    Source Data

    "A","","","","A,B,C,D"

    "X","X1,X1","20","+","X,B,C,D"

    "Y","Y1,B1","100","+","Y,B,C,D"

    "Z","A1","","","Z"

    • This reply was modified 7 months, 3 weeks ago by  sabarishbabu.
  • Well, that's unexpected ... but I have a solution for you. If you first REPLACE all instances of "" with " " (a single space) before using TOKEN, things seem to work (though you will have to decide what to do with the space ... set it back to NULL, perhaps).

    TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",1) 
    TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",3)
    TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",5)
    TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",7)
    TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",9)

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Thank you Phill !! Its working as expected I just added trim function 🙂

    TRIM(TOKEN(REPLACE(SourceRecord,"\"\"","\" \""),"\"",1))}

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

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