A more refined alternative, which does not run the risk of losing rows through aggregation:

SELECT t.Field0
      ,t.Field1
     ...

Another alternative

SELECT Field0
      ,Field1
      ,Field2
      ,Field3
      ,Field4...

Any thoughts on how can we accomplish this?

None whatsoever. You haven't provided DDL, sample data in the form of INSERT statements and desired output based on your sample data, nor...

Well done, but for future reference note that...

By 'betting', did you mean 'better'?

WinSCP is actually a rather good solution.

But if, by better, you meant easier to configure, I'd recommend this: https://www.cozyroc.com/ssis/file-transfer-task.

It's not free, but works well...

Also if I wanted to pass a literal string to a SQL field via SSIS do I just add another variable and assign the literal value to pass...

(DT_DATE) should do the job.

One more thing I changed my expression to (DT_Date)(LEFT(RIGHT(@[User::FileNameDate],14),10)) to convert to a date but it returns format like: 3/31/2021 (?) Are there other ways to convert the...

Considering you said earlier:

Assuming that there is no extension, what's wrong left LEFT(User::FileNameVariable,10)? For the above that'll be 2021-04-05. You...

Please take a read through here for a detailed walkthrough.

Thanks... within my Loop container I placed an "Execute SQL Task Editor" but I'm having trouble with its needed syntax in the SQL Statement and the Parameter Mapping......

In your Foreach container, you will configure a variable to be assigned the name (optionally the entire path) of the file being processed on the 'current' loop iteration. That is...

This should give you an idea.

In the Variables pane, I created a variable called FileName and assigned its value.

Then I created the variable FileNameDate and assigned its value as an...

No, you need to use SSIS's...

Do you have all the other Data Flow tools? Conditional Split, Merge, Multicast etc?