Reading hierarchical flat file into SSIS

Question

I have flat file that structured in a hierarchical format that looks something like this:

Area|AreaCode|AreaDescription

Region|RegionCode|RegionDescriptoin

Zone|ZoneCode|ZoneDescription

District|DistrictCode|DistrictDescription

Route|RouteCode|RouateDescription

Record|Name|Address|Ect

RouteFooter

Route|RouteCode|RouateDescription

Record|Name|Address|Ect

RouteFooter

DistrictFooter

District|DistrictCode|DistrictDescription

Route|RouteCode|RouateDescription

Record|Name|Address|Ect

Record|Name|Address|Ect

RouteFooter

Route|RouteCode|RouateDescription

Record|Name|Address|Ect

RouteFooter

DistrictFooter

ZoneFooter

RegionFooter

AreaFooter

I have to bring this into SSIS and consume information about the Record row and also about the header for the current record row. As well as information from several other sources and output a more simple flat file as a result.

I would like to read the flat file above into a structure that each row contains a record with the appropriate header information included.

My question is, what is the best way to do this if it is even possible?

Answer 1

First how do you tell what type of line you are on if you are on say line 3,987,986? How do you tell what is related to what? Is there apossiblity you could get this in a better format? Before spending lots of time (and don't kid yourself, this will take lots of time to set up and test properly) I would kick the file back to the provider and request it in a different format. You won't always get it, but you should at least try.

When I have done this in the past in DTS, the first characters of each line told me which structure the line referred to. I imported all into a staging table with two columns, one for the recordtype data and one for the rest. Then I parsed the rest into the staging tables for the type of record with the correct column structure for that type of record (and any fileds you might need to do the relationships) and then did clean up and then imported to prod tables. AS you also have differnt number of columns I would try that approach (only you may have to manually populate some columns instead of figuring out directly from the file), also give each record an identity filed in the staging tables. this will help you figure out the realtionships I think.