When working with SSIS and source or destination metadata changes SSIS does not reset the metadata it deletes the changed column and adds a new one at the end of the list. This results in messy packages after several changes have occurred.
To reset the metadata on data sources
- In the OLE DB Source, never select a table from the drop down box, always use a select query;
- To refresh the metadata in the source in this case, replace the select statement with select a = 1. Then, after the metadata is refreshed, paste back your original statement.