none
Reading a text flat file

    Question

  • I'm reading a text file that has some header rows and some detail rows, so representable:

    header1
    detail1 (of header 1)
    detail2 (of header 1)
    header2
    detail1 (of header 2)
    detail2 (of header 2)
    detail3 (of header 2)
    etc.

    I'm using the split conditional task to evaluate the first character of each row: if it equals to H I'm reading an header, D for a Detail.
    Now, I want to confirm that the text file is read sequentially, f.e the header1 is read (and saved in a SQL header table), then the detail1 of header1
    is read (and saved in a SQL detail table) and so on.
    Moreover, I want to put in the SQL table a row number referred to the current text row read and saved.
    Any suggests for me, please?
    Many thanks
    Wednesday, October 07, 2009 6:12 PM

Answers

All replies

  • Correct me if i am wrong , bottom line you want to select the 2 tables that are in your one CSV or txt file and insert them into 2 different destination table? if that is so then check
    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/20944506-5276-41e7-8b65-7f36d6e4bdaa

    this is not easy to do but possible.
    i have done it with excel.
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Wednesday, October 07, 2009 6:25 PM
  • Check John Welch's blog for a good example of dealing with your situation: Processing a Flat File with Header and Detail Rows.
    Additionally, to number the rows, try a Row Numbering component:
    Row Number - calculates a row number for each row, and adds this as a new output column to the data flow.
    Or just use a Script Component. 
    Todd McDermid's Blog
    Wednesday, October 07, 2009 6:28 PM
    Moderator
  • Ok, for your replies.
    But I have asked simply about the mechanism reading of a text flat file, with first a header and the related details, then another header with the related details and so on. I'm using a conditional split task.
    I wanto to be sure that the text file is read in sequential manner: first the first header, so I write in the related SQL header table, then the related details, so I write in the SQL detail table, then the second header, so I write again in the SQL header table and so on.
    Thanks

    Thursday, October 08, 2009 8:03 AM
  • Did you take the time to read the link to John's Blog?  That describes how to deal with your problem pretty much exactly as stated.  Flat file records are read in a sequential order, although the headers might be processed while the details are being processed this shouldn't be a big deal since everything will end up where it needs to get in the order in which it appeared (i.e. headers to header table, details to detail table).

    If, on the other hand, you really need to process these on a line by line basis, I would suggest using a .net application.  You could use the script task for this process.

    Please mark answered posts. Thanks for your time.
    Thursday, October 08, 2009 1:31 PM
    Moderator
  • Ok Eric. Do you put the link to John's blog?
    Thanks
    Friday, October 09, 2009 7:13 AM
  • No, I did - read it.
    Todd McDermid's Blog
    Friday, October 09, 2009 7:21 AM
    Moderator