Seeking Advice on Efficient Data Unpivoting in SQL Server with SSIS or Alternative Tools

I'm currently working with a SQL Server database and facing a challenge with transforming my data. The data structure requires me to unpivot numerous columns before moving it to a staging area.

I attempted to use SSIS for this task. While I'm aware that SSIS can handle unpivoting, the process seems cumbersome because I have to manually specify each destination column, and with so many columns involved, it becomes infeasible to write all of them manually.

I initially considered using T-SQL for unpivoting, but my SSIS package doesn’t directly execute T-SQL scripts the way I intended.

I’ve read that there might be a way to leverage C# within SSIS to handle such transformations more efficiently. Is that true? Alternatively, are there any other tools or approaches that you would recommend for efficiently handling this kind of data transformation within the SQL Server environment, possibly still using SSIS?

example:

Original -data

Unpivoted-data-

i do not want to write Unpivoted column names manually, i have more than 300 items.

Any tips or examples of similar experiences would be greatly appreciated. Thank you!

Here is an a sample using a bit of JSON (assuming 2016+). Note we only have to filter columns LIKE 'ITEM%'

The JSON portion in the CROSS APPLY will UNPIVOT virtually any table, view or query.

Example or dbFiddle

Select A.ID
      ,A.FirstName
      ,A.LastName
      ,Item = B.[key]
      ,Value = B.Value
      ,A.CategoryID
      ,A.LocationID
 From  YourTable A
 Cross Apply ( Select * 
                From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                Where [Key] like 'Item%'
             ) B