Yes, you can. No need to use Batch files/Shell/.Net, you can use a Dataflow.

In very simple terms: Use the Flat File Connection to connect to your text file,...

You say auditing but what is it you're trying to achieve? Are you trying to check it's in a valid format, for example? This might be more easily achieved by...

Spam is bad for you health. Containing 49% of your recommended Saturated fat!

That's in your Desired result, not your actual data. For example, is the below correct?
create table DataTable
  ([Site] varchar(5),
  [C_Date] date,
  Series int,

Think we need some more logic here. Perhaps you could explain how you get from your sample data to your desired output. What is 'ReCalc', for example, and how it...

Based on the very limited data we have. One idea:
CREATE TABLE #Sample
  (terminal int,
  [Date] date,
  [time] time,
  Amount decimal(8,2),

Couple of ways you could do this. Firstly, you could use APPLY and use a SELECT statement, and do something like this:
DECLARE @Date date = '20170106';
SELECT...