question

technette avatar image
technette asked

Data Import - Data Integrity

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.

ssisimport-datadtsetl
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke avatar image
Ian Roke answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

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".

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.