1

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.

2
  • Does your flat file have an ascending column value that you can use as the order in which to insert your data? Or some other way to ensure insertion order?
    – RLF
    Dec 19, 2015 at 2:20
  • @RLF the number of rows per "block" of data is always the same, and the blank line between "blocks" is consistent. So you would now that every X rows in the flat file represents all the column data for a single block of text. The actual data in the rows isn't incremental Dec 21, 2015 at 18:18

1 Answer 1

1

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.

2
  • I'll give this a shot, it seems like a logical approach! Dec 21, 2015 at 18:18
  • based on your answer to RLF, I would optimize step 3 to do 4 FETCHes in each cursor loop and then write them to output.
    – lvk
    Dec 21, 2015 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.