If you're asking if SSIS will automatically update the source definition when the file format changes it won't.

Well that subquery for temp table 2 is using a left join not an inner join to temp table 1.

Also, caps lock cruise control for cool.

It sounds like you need to do some serious level setting about what the job you're expecting him to do really is.  Troubleshooting and solving issues like SMTP or firewall...

Another option is to use TRY_CONVERT() if you want to make sure it'll actually convert to something in SQL server 

select 'yes'
where TRY_CONVERT(decimal(29, 9), @SearchVal) IS...

I'd be willing to bet this is the problem.

AND c.Record_Status <> i.Record_Status 

Anytime the record gets updated but the status doesn't change the update will...

Are you asking about the transaction logs or the server logs?

What are you trying to fix?  dbo is the default owner of those roles.