Back in the days of DTS it was possible to write a DTS package that altered itself dynamically at run time.  We tended to use this for things that are now standard components in SSIS but still, the ability to adjust to situations at run-time is a great boon.

I have to ingest JSON documents into our data warehouse and one of the challenges is detecting when a new key:value has appeared in the files supplied.  Technically everything I need to do is possible the bit that is sad is that I am building a technical solution to cope with a lack of communication.

Another useful facility is the ability to detect column orders in files and even have a thesaurus for column names.  3rd parties supplying data may be supplying Excel spreadsheets as a stop-gap until a formal solution is put in place.  The thesaurus facility allows for CustomerNumber, CustomerNo, Customer_Number etc.  The column detector allows for the CustomerNumber field (or any other for that matter) appearing in the 1st column on one supply and in some other column on subsequent supplies.