Hi,

I read the article "Handling Different Row Types In The
Same File" (http://www.sqlis.com/default.aspx?54). But it doesn't
talk about more complex flat files that have multiple headers, multiple
details, and where the column name and column values are on the same
row? It's more like a report having multiple pages on the same flat
text file. And then either export the information to XML or to a RDBMS?
Here's a sample flat file:



DATE 11/02/2006 TITLE
PAGE 1

Location: ABC TITLE 2

TITLE 3

CODE1 INPUT*************** INPUT DATA
----------------------------------------------------

DATE TIME EMP NO. EMP NO. ID NAME
MESSAGE

--------------------
---------------------------------------------------------------

20060801 05:22 11 11 ABC Benjamin Chi
AA BB CC DD



CC'S ADDED: ABC DEF HIJ



20060802 01:10 42 42 ABDD Rian Blue
ZZ BB AA DD



DATE 11/02/2006 TITLE
PAGE 2

Location: ABC TITLE 2

TITLE 3

CODE2 INPUT*************** INPUT DATA
----------------------------------------------------

DATE TIME EMP NO. DATA KEY

--------------------
---------------------------------------------------------------

20060801 05:22 11 1234567



CC'S ADDED: ABC DEF HIJ



20060802 01:10 42 ABCDEFG



CC'S ADDED: ABC DEF HIJ



I was wondering how SSIS can handle that type of file. I know the GXS
AI product can do it. Another product Informatica PowerCenter should be
able to extract the data as well.



Thanks,

Benjamin

I think I got it! but I still need to go further to validate that it works.
What I did was for each line of flat file source, there'll be a line number
associated, this is done via Script Component and user variable RowCount. The
output is simply the row# and the line data.

Then using conditional split, I split the headers, commands, details,
activities, etc to each Derived Column component to extract column data.

Then I'm planning to union all of them for further processing... based on
the row # I'll be able to group the data... more details to come when it
works.


"bchi49 (AT) gmail (DOT) com" wrote:

In addition, the derived records are insert to temp tables. Then on the
second data flow task it would query the inserted record tables. Using lookup
transformation to find the parent key (row number sorted in descent order -
getting only the top 1) querying and use Merge Join to create the
association/relationship between parent and child tables. Viola! the
relational model is created. Then going through several merge joins to create
the entire relational model for insertion to the destination tables. I
haven't insert them to the destination tables, yes, but it sounds like, I'll
need to insert the parent table records first, then get the real table key(s)
and then insert the childs, etc.

"Benjamin" wrote: