I want to know what tools or best BI methods are available for transferring data from one system to another changing the format in some cases, importing only new data for some fields...etc.
asked
Data Import - Data Integrity
Are you familiar with SSIS? SQL Server Integration Services is the best method of transfering information from different systems to SQL Server and vice versas.
You may also come across BCP which was superceded by SSIS but is still widely used.
What you have just described is better known as ETL - Extraction, Transformation & Loading.
In SQL 2005 and 2008 the tool to use is SSIS. In previous versions (SQL7 & 2000) it was DTS.
You'll find lots of useful info in Books Online (BOL) the offline help that is shipped with SQL Server, and online at places like MSDN. Here is a link for example showing how to create a simple ETL using SSIS. It really is a much bigger topic than I can cover here, and would suggest some further research over on the SSC main site, searching for keywords such as "SSIS", "ETL".
I agree with Kev and Ian's responses whole-heartedly.
I would add though that there are times when small amounts of data need to be brought in to SQL Server from a variety of sources and something more agile than SSIS can be quicker to configure and execute. I am a fan of LogParser (details here), its a command line application that will import almost any data source, allow conversion of data types and export to a variety of destinations - including SQL. There is also PowerShell (details here), I keep trying this but find it less intuitive and therefore use it less frequently. Again it is a command line interface although there are GUIs available.
Having them all to hand when you get a data import/transfer request means you can pick the most appropriate one to each task.
