Whether the database server is local or remote is not important to Advantage Data Transformer. What is important is that the database can be accessed by ODBC and that the DataSource connecting to the database uses a driver appropriate for that version of SQL Server. For SQL2008, the right driver is "SQL Server Native Client 10.0".
It is best not to use a driver whose version is lower than the database, but it's okay to use a driver whose version is higher than the database version because Microsoft SQL Server drivers are backward-compatible. So the "SQL Server" driver can be used with SQL2000 and below, " SQL Native Client" driver is for SQL2005 and below, while the "SQL Native Client 10.0" driver is for SQL2008 and below.
If the SQL Client installed on the ADT Server host computer is an older version than the database you wish to connect to, that's not a problem; however, you will need to get a copy of the latest ODBC driver. Microsoft offers these packages free at its download center. The site for getting the SQL2008's Native Client 10.0 driver is:
Scroll down to find the "Microsoft SQL Server 2008 Native Client" pack. Click on the "X86 Package" download link and install.