A new managing director has been appointed to lead a major railway programme in the North of England.