Database, SQL Server

SSIS: How to include RowDelimiter with Destination Flat Files

This article uncovers an issue when using SSIS Destination Text File to output rows from Database.

To get output in separate rows in the destination text file here is what you have to do. Setting the property “RowDelimiter” to “{CR}{LF}” for the text file connection will not make any difference. The Fixed width with row delimiter option is not available in the Flat File Connection Manager Editor. If necessary, you can emulate this option in the editor. Here is how you do it.

  • Select an existing connection manager by using the list box, or create a new connection by clicking New.
  • Create a new connection by using the Flat File Format and Flat File Connection Manager Editor dialog boxes
  • On the General page of the Flat File Connection Manager Editor, for Format, select Ragged right.
  • Then on the Advanced page of the editor, add a new dummy column as the final column of data.
  • Now for ColumnDelimiter put “{CR}{LF}“, this will force a line break to insert each row on separate line.
  • Finally go to your Data Flow Component and check the mappings. This new column should not have any mapping.