• Jay@Work (10/20/2016)


    Not sure yet, this is a work in progress. Given the nature of the data the number of columns returned from the source would be variable as would the values.

    This may help explain it better.

    The data revolves around building work.

    The main columns will be Owner, Address, Work Type, Completion Date.

    The extra data (where the one to many comes in) is around trades people who worked on the building work

    So 1 row may look like

    John, 123 some place, new roof, carpenter, Steve, Tiler, Jim

    Another may be

    Fred, 99 Other Place, Build Garage, Carpenter, Jill, Electrician, Jack, Painter, Bill, Drain Layer, Andrew

    So the number of trade columns is variable could be 1 set (trade type/name) could be 20.

    In the source database these records are joined by a many to many table.

    TradeType, WorkID, TradesPersonID

    I'm wondering if I am overcomplicating things by trying to denormalise this - perhaps it would be better to duplicate the structure in the destination database?

    Work

    TradePeople

    Work/TradePeople.

    I have yet to design the destination DB or the SSIS queries to populate it

    I see you're already starting to realise that you're idea is not doable.

    Try to decide of further details - not only number of columns, but their names, data types, try to write a query to find out who was doing roofing job for a site - it will help you to ditch this approach for good.

    _____________
    Code for TallyGenerator