I am stuck in selecting SSIS package for splitting values of a column

Musadique Shah 1 Reputation point
2021-06-30T06:56:54.79+00:00

Column: cast
Row1: Joi£o Miguel, Bianca Comparato, Michel Gomes, Rodolfo Valente, Vaneza Oliveira, Rafael Lozano, Viviane
Porto, Mel Fronckowiak, Sergio Mamberti, Zezi© Motta, Celso Frateschi.
Row2: Demii¡n Bichir, Hi©ctor Bonilla, Oscar Serrano, Azalia Ortiz, Octavio Michel, Carmen Beato
Row3: Tedd Chan, Stella Chung, Henley Hii, Lawrence Koh, Tommy Kuan, Josh Lai, Mark Lee, Susan Leong, Benjamin
Lim
Row4: Elijah Wood, John C. Reilly, Jennifer Connelly, Christopher Plummer, Crispin Glover, Martin Landau, Fred
Tatasciore, Alan Oppenheimer, Tom Kane
Row4: Jim Sturgess, Kevin Spacey, Kate Bosworth, Aaron Yoo, Liza Lapira, Jacob Pitts, Laurence Fishburne, Jack
McGee, Josh Gad, Sam Golzari, Helen Carey, Jack Gilpin
Row5: Erdal BeÅŸiki§ioÄŸlu, Yasemin Allen, Melis Birkan, Saygın Soysal, Berkan Åžal, Metin Belgin, Ayi§a Eren,
Selin UludoÄŸan, i–zay Fecht, Suna YıldızoÄŸlu

  • I have this only one column in my excel sheet which contains 5k + Rows that i have pushed in SSIS,
  • I want to transform the data in SSIS, I want all these Row values in a single column & Distinct as well.
  • Kindy help me through this, As i am a beginner in SSIS.

looking towards your kind help.

Thanks & regards

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,450 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,426 Reputation points
    2021-06-30T08:27:52.047+00:00

    Hi @Musadique Shah ,

    What's the destination of the source? SQL database or excel?

    It's a little hard to meet your requirement in SSIS.

    You may convert the excel data to SQL database and then use tsql to deal with the data.

    110572-screenshot-2021-06-30-162528.jpg 

    insert into  dbo.[newcast]  
SELECT STRING_AGG(value,',')  
  FROM (SELECT distinct value  
FROM [OUTPUT].[dbo].[destinationcast]    
    CROSS APPLY STRING_SPLIT([cast], ','))a

    Note: The function is only work after SQL2017.

    110529-screenshot-2021-06-30-162451.jpg

    If your destination source is excel, you may also convert the table in SQL database to excel with SSIS.

    Regards，

    Zoe

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

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

    0 comments