This is not particularly elegant, but it seems to get the job done.

Next time, please remember to post your sample data in consumable format, so that others can simply paste...

As I suggested in one of your other threads, SSIS needs to 'see' the table it is going to load data into at design time, not at run time.

Do the...

Here are the results of the queries you provided. Now tell us what the logic is to get from here to the results you require, as it is not clear...

Use an...

Assuming your target column's datatype is 'DATE', it does not have a display format.

If your import is converting formats from DDMM to MMDD, you would get lots of errors for...

There is something strange going on here.

Are you trying to create a table within a data flow? (I see a Mappings node)

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