none
Sample on how to import Flat File with vertical structure

    Question

  • Hello,

    I'm looking for an example on how to import a Flat file that is not a csv file but had data on several lines, a small indicator at the beginning of the line indicates what kind of data is on the rest of the line. Not every field is availabye for each block.

    Anybody got an idea on where to find this kind of samples.

    Thx.

    Monday, April 24, 2006 12:52 PM

Answers

  •  Harry_Leboeuf wrote:

    Hello,

    I'm looking for an example on how to import a Flat file that is not a csv file but had data on several lines, a small indicator at the beginning of the line indicates what kind of data is on the rest of the line. Not every field is availabye for each block.

    Anybody got an idea on where to find this kind of samples.

    Thx.

     

    I personally would import each row as simply a single column of type DT_STR/DT_WSTR and then parse out the required columns within the data-flow - probably using a script component.

    You could make things slightly easier for yourself by importing the data as two columns: the first contains the indicator and the second contians the rest of it.

     

    Alternatively you could combine those two steps into one and write a source component that imported the data and parsed it on the way. I wouldn't do that though cos there's no point in creating work for yourself - let SSIS import the data and you are then left with the relatively easy job of parsing out the columns.

    -Jamie

     

    Monday, April 24, 2006 3:20 PM

All replies

  • 
    Harry,
     
    I think the inconsistency in which columns are present or not in each row is going to cause you problems in SSIS.
     
    If I understand your structure correctly you can't use the Flat File Connection manager for such data. I think you'll need to write some custom code. Without knowing the structure of your data I don't think I can be more specific.
     
    Andrew Watt [MVP]

    Hello,

    I'm looking for an example on how to import a Flat file that is not a csv file but had data on several lines, a small indicator at the beginning of the line indicates what kind of data is on the rest of the line. Not every field is availabye for each block.

    Anybody got an idea on where to find this kind of samples.

    Thx.

    Monday, April 24, 2006 2:56 PM
  •  Harry_Leboeuf wrote:

    Hello,

    I'm looking for an example on how to import a Flat file that is not a csv file but had data on several lines, a small indicator at the beginning of the line indicates what kind of data is on the rest of the line. Not every field is availabye for each block.

    Anybody got an idea on where to find this kind of samples.

    Thx.

     

    I personally would import each row as simply a single column of type DT_STR/DT_WSTR and then parse out the required columns within the data-flow - probably using a script component.

    You could make things slightly easier for yourself by importing the data as two columns: the first contains the indicator and the second contians the rest of it.

     

    Alternatively you could combine those two steps into one and write a source component that imported the data and parsed it on the way. I wouldn't do that though cos there's no point in creating work for yourself - let SSIS import the data and you are then left with the relatively easy job of parsing out the columns.

    -Jamie

     

    Monday, April 24, 2006 3:20 PM