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