After a bit more digging I have come to the conclusion that SSIS is the best way to do this, so I have added a Transformation script component and done the work in there :)