Normalize a WIDE table into 10-15 tables

I have a table on an AS/400 iSeries Database that I am wanting to normalize. Currently it has approximately 141 columns. Because of the way it was designed and then modified over the years it has grown into a very ugly monster. When users want to make vendor name changes, manufacturer changes etc they are having to change multiple rows thus possibly creating errors.  My though was as follows:

        1.) Using SSIS import the AS/400 table into a staging table in SQL

        2.) Apply some methodology to break the fields apart putting a foreign key from the breakout tables into the master table.

example:

Denormalized table structure:

        SKSKUN N(7,7)

What was your question?

Dustin,

  Hmm... Not sure why but it only posted part of my message... I will have to recreate it...

Thank you,

AmcAmx

I have a table on an AS/400 iSeries Database that I am wanting to normalize. Currently it has approximately 141 columns. Because of the way it was designed and then modified over the years it has grown into a very ugly monster. When users want to make vendor name changes, manufacturer changes etc they are having to change multiple rows thus possibly creating errors.

My thought was as follows:

    1.) Using SSIS import the AS/400 table into a staging table in SQL

    2.) Apply some methodology to break the fields apart putting a foreign key from the breakout tables into the master table.

           example:

                        Denormalized table structure (I am only showing some of the fields):

                                OLD PRODUCT TABLE

                                        SKSKUN N(7,7)

                                        VENDOR CHAR(200)

                                        CLASS CHAR(2)

                                        MANUFACTURER CHAR(200)

                                  DATA -     999999, "Williams Construction", "23", "Hong Chow"

                                                 938239, "Williams Construction", "33",  "Billings Mfcturing"

                                                 923020, "Tee Top Trikes", "12", "Bike Makers"

                                                 238432, "Tee Top Trikes", "31", "Bike Makers"

                        NEW Normalized Structure

                               PRODUCT

                                             pkProductId INT IDENTITY(1,1)

                                             ProductSku   VARCHAR(10)

                                             fkVendorId INT

                                             fkClassId INT

                                              fkManufacturerId INT

                               Vendor

                                            pkVendorId  INT IDENTITY(1,1)

                                            Vendor CHAR(200)

                                            VendorName VARCHAR(200)

                               Class

                                            pkClassId  INT IDENTITY(1,1)

                                            Class CHAR(2)

                                            ClassDescription VARCHAR(100)

                               Manufacturer

                                            pkManufacturerId  INT IDENTITY(1,1)

                                            Manufacturer CHAR(200)

                                            ManufacturerName VARCHAR(200)

My idea is to take the OLD Product table and pull from it the necessary pieces to create the new tables (Vendor, Class, etc) and then go back to create the NEW Product Table by linking the Fields in the OLD Product table to the new normalized tables assigning the new PK for each of the tables into the corresponding field in the new Product table. 

If this is the best way to do this, how do you go about doing something like this in SSIS (which controls would be the best to use) ?

If this is not the way you would accomplish this could you please point me to the method that you would take ?

Thank you,

AmcAmx

Sounds like you will need some conditional splits to send the rows to the correct tables. Probably some derived columns to manipulate the rows and columns.