SSIS =- How to process an CSV input file with a variable number of columns based on record type (Header, Detail, Trailer)?

Unfortunately this is a common problem. And only way i've found around it is to configure a flat file connection manager with a single varchar column. Put the entire row into the string column Then use a conditional split to distingush between header/footer/data row, and direct each type to separate outputs You then need to either write the data to a file and then do another connection manager to it and process it, or use a derived column to split up the single string (varchar) into the multiple columns using string manipulation, string split or substring etc etc.

Unfortunately this is a common problem. And only way i've found around it is to configure a flat file connection manager with a single varchar column. Put the entire row into the string column.

Then use a conditional split to distingush between header/footer/data row, and direct each type to separate outputs You then need to either write the data to a file and then do another connection manager to it and process it, or use a derived column to split up the single string (varchar) into the multiple columns using string manipulation, string split or substring etc etc.

This process has worked for us in the past. – Sam Jun 9 at 23:48.

It is possible to do this in SSIS but involves coding using Script Component. Please see my answer in this Stack Overflow question. The example loads a file with varying lengths and they all have relations tied to each other.

The delimiter in the example is asterisk (*) but in your case it will be comma (,). Hopefully, the example might give you an idea. If not, let me know so I can post an example matching your data.

Hope that helps.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.