Something like this?
SET @j = JSON_MODIFY(@j,'$[0].MediaCaption' , N'hi')

What does,  or should, the script task do?

Sure, I understand. I remember how long it took me to get going with it. Have you looked at the relevant Stairway series? It might help walk you though.

As far as I know, TFS stores all data in SQL Server databases.
If you can't work out how to query this data in T-SQL, I can't see how SSIS...

I suggest that you work out how to do it outside of SSIS first. Have you already got that bit worked out?

Presumably you were trying to import from Excel? If you are just starting out with SSIS & want to 'get some runs on the board', I suggest that you start...

If it were me, I'd probably process the data from your vendor into a table which has been normalised and then use the normalised table as the basis for all...