Few points
😎
polkadot (5/2/2015)
1) Import the file line by line into a staging table and parse it from there. This method is common when importing multi-document-type files.
The file uses the thor character (non standard) as a delimiter so I have to scrub the file first before loading to sql and the warning is given during the scrub process (comprised of flat file connection, derived column, flat file destination). the task looks like it succeeds but it doesn't write the rows to the subsequent flat file which I need to upload to stg.
Suspect this could be part of the problem, add data viewers on both sides of the derived columns and check if the output matches the full input. There should be no need to clean / replace the thorn character, just configure it as a delimiter for the source.
2) Read the file line by line into a Script transformation and to the splitting there, works similar to the one above.
does this require visual b or c# code?
Yes, you can use either VB or C#, in fact this would replace the scrubbing step.
3) Read the file and count the delimiters in each line, add any if missing from the count in the first line.
no delimeters are missing
Then my suspicion is that the scrubbing step is the culprit, have you tried to append a single empty line to the file after the scrub?