Transfer data from Excel to MS SQL Server using DTS(Data Transformation Services)

 MS SQL Server 2008 tutorial :  DTS (Data Transformation Service in SQL Server)

For those who dont know DTS(Data transformaiton service), its a MS SQL Server tool to import/export data to and from MSSQL Server tables from various sources like Microsoft Excel file, Access file, another flat file, or any other MSSQL Server table.
This post will will make all the doubts clear regarding DTS in sql.

Now to demostrate DTS we will take a following example.
Consider you want to transfer a data from Excel sheet to MSSQL table. Like the one below

Fig 1: An Excel Sheet example

For sake of explanation the XL sheet contains only 10 records. But actually it can contains thousands and lakhs of data.

Now before we start the process there are few pointers that should be checked/remembered, which are as follows:

• The XL sheet that you needs to be exported to sql must have Column names similar to column names of table present in MS SQL Server 2005/2008 database. The reason?  explained later in this post.

Fig 2: Table in SQL server with same Column names

In XL sheet column header must be followed by a "Dummy data row". Notice that in Fig 1 there is no dummy row. Now why do we need this dummy row. According to working dts sql service checks the XL's first few row's (first 8-16 rows to be precise) and counts which row has bigger data in terms of characters and sets it max, thus if your table contains max character of rows in 30th record dts will throw an Truncation error. Thus we set a dummy row large enough to tell dts that this is the largest data row in our table. After putting dummy row the XL sheet looks like below

Fig 3: Excel Sheet with Dummy data row

Now we are set. To start..open SQL Server Management Studio (2008 R2 here in my case).
>> Right click the database where the table exists and select "Import Data". This is the way i prefer. Show in figure below.

Fig 4: Navigating to Import/Export wizard

After this the Import / Export wizard opens up.

Fig 5: the Import/Export Wizard

Click Next and in next window select "Microsoft Excel" in Data Source (shown in fig 6) 



Fig 6

Now look at fig below(fig 7)... the check box marked in red..read what it says...."First row has column names ". Yes .. this is the one reason why we kept the column names similar... but the actual reason is still ahead...

Fig 7

After selecting Data Source, Browse the XL sheet  and click next...Let the Destination and Server name be same if you navigated like fig 4.  Note: The Destination changes depending upon Operating system and how you have installed MS SQL server. (shown in fig 8)

Fig 8

After Clicking Next below screen appears. let the first radio option be selected (shown in fig 9)

Fig 9

In next window select appropriate sheet on left and appropriate sql server table on right. (As show in fig 10)

Fig 10

Now on the same above window dont forget to Click "Edit Mappings". When you click it a window similar to fig shown below will open up depending on which version of sql server you are using..

Fig 11

Now if your XL sheet and sql server table column names match the source and destination will match automatically as in this example show in fig above.This is second reason why we kept XL and sql server column names similar, because if you have more columns in XL we can keep matching them here in this wizard. If the column names donot match the destination column in above window shows "ignore". Dont worry you can still match them if you have missed one or another. After you click Ok next window appears

Fig 12

Dont forget to select ignore options for last two drop downs at the end of window. After you click next following window shows up.

Click Next and following window shows up

After you click finish the actual process starts..

Oppsss!!!! and error occured.

To know more about error Click "Messages"

In this case following was error

After reading the error carefully, i came to know that we forgot to provide additional information in window shown in  fig 11

Now why did the problem occured? Because our excel sheet contains number in "cust_id" and cust_id is primary identity field in sql server. Thus we need to tell dts that we are allowing to put identity field through XL sheet. Everthing in this case would have gone right if cust_id was not present.

Any ways if all things go right then the following screen appears

Now go check the data base table...

If you have any doubts leave a comment...