The conversion has to be done on a new, derived, column. The resultant should be date.

Is use (DT_DATE)(SUBSTRING(Col11,4) + "/" + SUBSTRING(Col15,2) + "/" + SUBSTRING(Col1,7,2))

which breaks it into a string of yyyy/mm/dd and this converts to any one of the date formats.

As for the 00000000, you would have to evaluate it first and if 0000000, set it to null else try parse it.

Unfortunately, you are going to have to do this to all columns.

if this is a once off thing, rather import into a tempt table as a varchar and do the SQL to do the conversion. Would be quicker.