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...

Have a look at this. It assumes that RelatedLoans.SoldLoan cannot also exist in the MasterLoan column:

DECLARE @MasterLoanData TABLE
(
  NoteNumber VARCHAR(10)
, Balance INT
);

DECLARE @RelatedLoans...

Your UPDATE statement has a single target (r)

Yet your statement is attempting to update columns from m1 and m2.

As for the link that Phil gave - yes the Integration Services SERVICE is not required to run the packages - this is because from SQL 2012 SSIS catalog was...

You've been here long enough to know how to do this. 
Here is a formatted version, for all those who struggle to optimise embedded SQL:

DECLARE @PatientVisit_ID...