x

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.

more ▼

asked Feb 09, 2010 at 04:22 PM in Default

technette gravatar image

technette
1.2k 86 99 109

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

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

more ▼

answered Feb 09, 2010 at 04:54 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

For conversion into standard formats (xls, csv, etc) then SSIS/DTS is probably one of the best ways to go. If you need a highly detailed custom format then it often helps to use a full programming language such as Python, Perl, C#, etc.

Of course, SSIS is highly scriptable so you probably could use it, but I find Python and other general purpose languages better suited for sophisticated data processing and formatting.
Feb 10, 2010 at 01:28 PM TimothyAWiseman
Thank you very much. I have alot of work to do here, testing and demonstrating proof on concept. It's nice to get feedback from professionals.
Feb 18, 2010 at 07:41 PM technette
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 09, 2010 at 04:37 PM

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 10, 2010 at 06:23 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x943
x67
x66
x29

asked: Feb 09, 2010 at 04:22 PM

Seen: 3304 times

Last Updated: Feb 09, 2010 at 04:54 PM