Share via

SSIS Derived Column

kityprincess 21 Reputation points
2022-09-07T23:05:58.753+00:00

I've been searching all day but I have not been able to find an answer to my question. We're running SSIS 2016. I have a xls file I'm importing that has a field that's customer. There can be one or many customers listed in this field. I need to get all the customers into their own fields. How can I do this?

I.e.
SalesPerson Customer(s)
Johnny Appleseed Spin Masters;Fidget Toys ; Umbrellas ;Lots of Balls

to

SalesPerson Customer1 Customer2 Customer3 Customer4
Johnny Appleseed Spin Masters Fidget Toys Umbrellas Lots of Balls

TIA

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,702 questions
0 comments
Accepted answer
  1. ZoeHui-MSFT 41,491 Reputation points
    2022-09-08T02:16:10.783+00:00

    Hi @kityprincess ,

    If your data source is like below:

    238876-image.png

    You may consider to use token expression to split the column with ‘;'. 

    TOKEN([Name_of_your_Column], "；" , 1)

    238845-image.png

    238875-image.png

    If the data source is like below, it is hard to split the column due to the inconsistent delimiter. You may adjust first in your excel and then load into SSIS.

    238864-image.png

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    1: /api/attachments/238823-image.png?platform=QnA 3: /api/attachments/238823-image.png?platform=QnA

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.