Thanks Ian, Sage's ODBC has always been a bit quirky and from what I've read Sage blame Microsoft and Microsoft blame Sage!

I'd already tried running a data transformation task and importing the data into temp tables but or just over 2000 rows it took 30 - 40 seconds to complete and multiple users would be running it frequently which wasn't acceptable to the users so I've eventually ended up with a data diven query task in DTS that updates the SQL table using a join with the live Sage tables.

Updates take around 1 or 2 seconds now so we can live with that.