Need your expert opinion on normalizing a flat file

• Jason-356205

  SSC Veteran

  Points: 281

  More actions

  October 16, 2008 at 3:46 pm

  #124290

  I have a CSV file with parents and children in the same row, similar to the structure below:

  ParentFirstName, ParentLastName, ParentSSN, Child1FirstName, Child1DOB, Child2FirstName, Child2DOB, ...

  I need some expert opinions on the best way to split this flat file into two different tables (data outputs in the Data Flow) in SSIS. The tables I want to create will look similar to this:

  Table 1 (Parents):

  ParentFirstName, ParentLastName, ParentSSN

  Table 2 (Children):

  ParentSSN, ChildFirstName, ChildDOB

  The actual file has 204 columns with space for 19 children and lots of additional info. Needless to say, I'm looking for the most streamlined and optimized way to accomplish this in SSIS.

  Thanks for your help!

  Jay

• COZYROC

  One Orange Chip

  Points: 28499

  More actions

  October 24, 2008 at 4:25 pm

  #889545

  1. Setup flat file connection manager to the CSV file.

  2. Insert data flow task.

  3. Insert flat file source component.

  4. Insert multicast component and connect to the source component.

  5. Setup 2 destination components (oledb most probably) and connect to the multicast component.

  6. Setup your destination result.

  Let us know if it worked as expected.

  ---
  SSIS Tasks Components Scripts Services | http://www.cozyroc.com/