Dealing with flat files

Unlike While importing flat files in DTS (row parsing) SSIS deals with Column parsing, as such there are things to be taken care of else we will end up in spending days searching for workarounds:

1) If you have a Text file having rows shown below:

ColA, ColB, ColC
1, The,
2, Thee,We

Here you can avoid the part of 2nd row getting sucked into first row while confiuring as shown below...

ColA, ColB, ColC
1, The,2,
Thee,We

Either specify the Column delimiter for the last column as new line {CRLF} in the Advanced options for Flat file connection manager
or
Mention the Row delimiter as ragged right, so that all Columns are merged into a string array suggesting an estimated rowsize for resulting column and then use a script component to further to split into required columns

2) Do you want load images into a Database using Flat files, here you go

3) Workarounds for common errors:

1) Error: Cannot Convert between unicode and non-unicode or vice versa
One way we can use a data converion transformation to convert type DT_STR(unicode) to DT_WSTR or vice versa and the Second way is to change the types in Advanced editor for OLEDB Flat File Source.

2) Error: Cannot convert due to Potential loss of data.....
Manually changes the Column types of Flat file source to match that of the Destination Column types.

3) Error: Text was truncated or one or more characters had no match in the target code page....

Bob bojanic answers this in SSIS msdn forums that the flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file."
Note: However, make sure after sampling the rows using suggest types in the advanced options dont apply ok as it will change the metadata entirely, Only use this as a reference. Manually edit the size of the Column Metadata in the Advanced Editor.