Put column data into the correct destination column sql ssis

Question

I am having some issues with order data from a system - different column type error.

I am using SSIS to extract the data into a table

However I am faced with some records looking like this

CUSTOMERID DATE       ITEMID     ITEM 
1          10/04/2017 A1456      TABLE 
2          20/05/2017 A3432      CHAIR 
3          16/06/2017 A3432      CHAIR 
4          18/06/2017 Null       A3432,CHAIR 
5          23/06/2017 A1456      TABLE

Is there some SQL code, or a process I can use in SSIS that can help correct the issue with ID 4 to put the ITEMID into the correct row and separating the item as follows?

CUSTOMERID DATE       ITEMID     ITEM 
1          10/04/2017 A1456      TABLE 
2          20/05/2017 A3432      CHAIR 
3          16/06/2017 A3432      CHAIR 
4          18/06/2017 A3432      CHAIR 
5          23/06/2017 A1456      TABLE

Answer 1

You can simply add 2 derived column with the following expression:

ITEMID

ISNULL([ITEMID]) ? TOKEN([ITEM],",",1) : [ITEMID]

ITEM

TOKENCOUNT([ITEM],",") > 1 ? TOKEN([ITEM],",",TOKENCOUNT([ITEM],",")) : [ITEM]

References

• TOKEN (SSIS Expression)
• TOKENCOUNT (SSIS Expression)