locked
Parsing a segmented text file using SSIS into smaller files RRS feed

  • Question

  • I have a text file that has a layout for fixed length columns but the issue is that the file has repeating segments like a HL7 file.

    Example:
    ===========

    H Header

    P Patient Address City State Zip I Insurance Name Address

    P Patient Address City State Zip I Insurance Name Address

    P Patient Address City State Zip I Insurance Name Address

    T Trailer

    I don't think the bulk insert and format file can read the repeating segments between Header and Trailer. I think if I can parse each of the repeating segments into their own text file then I can use bulk insert and the format file to read the data into the database. I would love somebody to tell me I am wrong, PLEASE, and I can use bulk insert and formatting files without having to parse the text into multiple files: otherwise, would someone tell me what I need to use to parse the text file in SSIS.

    Thanks,

    Sue


    Sue

    Thursday, September 12, 2013 8:33 PM

Answers

  • How will you do parsing of this file into multiple files?

    I think you have this options:

    1. using script transformation in data flow task and then custom handling this complex file structure

    2. bulk loading this file into database as a single column file and then in sql script go through records and determine what to do with each (maybe this can be done with as many select queries as you have different sections in file)

    I would go with option 2 because I am lousy with writing script tasks.


    Regards, Dean Savović

    • Marked as answer by OutThere Monday, September 16, 2013 6:05 PM
    Thursday, September 12, 2013 8:57 PM

All replies

  • Yes Sue, unfortunately, you have to make the data structured 1st. Otherwise SSIS would not pick the schema up.

    Specifically, you need to split it into chunks. For example, you can use the Conditional Split component to do so.

    More on that technique is here:

    http://sqlage.blogspot.ca/2013/07/ssis-how-to-use-conditional-split.html

    The key is in 1st Flat File connection manager defining the input file as having 1 column, then you split the files and take these as having multiple columns into processing.


    Arthur My Blog

    Thursday, September 12, 2013 8:53 PM
  • How will you do parsing of this file into multiple files?

    I think you have this options:

    1. using script transformation in data flow task and then custom handling this complex file structure

    2. bulk loading this file into database as a single column file and then in sql script go through records and determine what to do with each (maybe this can be done with as many select queries as you have different sections in file)

    I would go with option 2 because I am lousy with writing script tasks.


    Regards, Dean Savović

    • Marked as answer by OutThere Monday, September 16, 2013 6:05 PM
    Thursday, September 12, 2013 8:57 PM
  • Dean,

    I am lousy at script tasks too so suggestion number 2 worked well for me.

    Thanks,

    Sue


    Sue

    Monday, September 16, 2013 6:06 PM