question

MusadiqueShah-4567 avatar image
0 Votes"
MusadiqueShah-4567 asked ZoeHui-MSFT answered

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

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @MusadiqueShah-4567,

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




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.