Sql-server – How to set SSIS flat file connection row delimiter to blank line

etlsql serverssis

I have a text file with the following format:

DataColumn1
DataColumn2
DataColumn3

DataColumn1
DataColumn2
DataColumn3

DataColumn1
DataColumn2
DataColumn3

Each row with text in the file delimits a column of data, and each row with no data delimits a row.

I'm trying to import this data into an MS SQL database table using SSIS, but I'm struggling with the flat file connection manager being able to understand this delimiter formatting the upstream process I'm depending on gives me.

Best Answer

You can't specify a ROW (even empty) as a row delimiter. The problem is not really with the flat file connection. Actually there is another problem here than just row delimiter - you need to combine multiple rows of input and "pivot" them to create one row in the output table.
This calls for multiple steps in your package:

  1. create a temp table with one col to hold the input file.
  2. input the raw data into that temp table.
  3. write some SQL code (i would encapsulate it in a stored proc) to process the raw input and build rows of data. this needs to be done in using a cursor. you loop thru each input and build a record until you reach an empty input. then you write out as output. continue until no more records in temp table.

Hope this helps.

Related Question