The variant with flat files with dangling right font is fine for one final field terminated by CRLF, but when any number of terminating fields that are empty is suppressed and the line ends earlier with CRLF, I did not find an alternative in SSIS, but for read a few columns in a single REMAINDER column, and then use the Derived Column transform with SUBSTRING operations to retrieve the “optional” columns one by one.
One of the possibilities that has arisen for me is that there is some way to pass the string through the padder component to SSIS before the connection manager delays it. You can then get all the benefits of using Connection Manager to adjust the length of the incoming columns. I could always create an external program (or a completely separate SSIS data stream) to transfer a file through which each line will be broken with spaces (and with broken rights - just adding the same minimum number of spaces to account for all suppressed fields for each line will be sufficient) , but it seems rather wasteful for disk space, and also requires the file to be read (and written) completely again.
Alternatively, there may be a third-party component of the data source that will be just NULL, or empty, and the columns will automatically if the line ends prematurely.
Any other options?
source share