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

-o || Programmer || o-

To be a good programmer is difficult and noble. The hardest part of making real a collective vision of a software project is dealing with one’s co-workers and customers. Writing computer programs is important and takes great intelligence and skill. But it is really child's play compared to everything else that a good programmer must do to make a software system that succeeds for both the customer and myriad colleagues for whom she is partially responsible. In this post I attempt to summarize as concisely as possible those things that I wish someone had explained to me when I started developing interest in developing programs.

I have being programming since my school days. I remember writing small programs in BASIC(for those how don’t know BASIC is ) and LOGO(it was a meant to draw different geometrical shapes using commands) . But actually later I came to know LOGO was more than that. I remember very interesting thing about LOGO, the cursor which we see now a days in LOGO there used to be a triangle which was called as “Turtle”. Anyways even in those days that programs would seem more difficult. I read an interesting article about programmers which I would like to share…

Signs that you shouldn't be a programmer:
The following may not have any remedies if you still suffer from them after taking a programming course in school, so you will stand a better chance of advancing your career by choosing another profession.

1. Inability to determine the order of program execution
Symptoms

a = 5
b = 10
a = b

print a

1. You look at the code above and aren't sure what number gets printed out at the end

Alternative careers
1. Electrician
2. Plumber
3. Architect
4. Civil engineer

LOL.. so all "wanna be" programmers would now have decided an Alternative careers. So in my upcomming post i am going to start a "How to" guide for programmers right from debugging a code to performance fix and lots more...

Ubuntu to rescue...

Hello all,
Linux has being a great competitive as well as alternative OS to Windows/Mac etc.. Many things you can do with Linux and today  I want to share this experience with you all.

But before this, a short trailer of how i found myself in soup and how Linux save the day.. Read on...

Into the Problem:
Day before yesterday i was extending my C drive using Disk management utility. my C drive at that time was 15Gb :P, Since i need developing tools on my Laptop C drive need more space. Following are list of my tools i use with its approx disk usage
* MS SQL Server 2008 R2 - takes almost 2.5GB
* MS Visual Studio 2008 - 6GB
and some times Visual studio 2010 which takes whopping 7 GB... :/ and other such tools...

OK, now back to point, I couldn't extend the C drive for some reasons. So my friend suggested to use Partition Magic to  extend or shrink a Drive.. Never needed to use partition software's.. it was first time i was going to use it. I downloaded it from Torrent z as usual.. Next was least expected or my bad luck.. when i ran the setup, an error occurred saying Drive label not found. When i opened the Windows explorer i  was shocked.. it was showing only my C drive and all my other drive (D: E: F:) were missing... I didn't knew what to do that time, so as usual to solve any problem in windows i restarted my laptop... and this was another mistake i did... i knew problem was related to Partition table.. and after that my OS didnt boot.. The error was "Operating System not found". It was disaster for me.. all my data was there in hard disk but i couldn't access it. So instead of setting up new Win 7, i decided to create a LiveUSB of Linux...

Before the problem existed i was using Ubuntu 11.04(Natty Narwhal) on diffrent partion. Finally created a LiveUsb of Ubuntu , backed up all my data on my portable HDD and now there was no problem to format my Laptop hard disk... So during formating i even partitioned drives, installed Win7 again, and my machine was up again for working..
-----------------------------------------------------------------
Lessons learned :

* Never trust and download softwares from sites which you are not sure of.
* If you dont know how to use some software, take help of a technician.
* And even in worst case dont panic.. "Always carry your brain with you"

In my next blog i will give all the details of how to create LiveUSB linux.

Bored... Play Tic-Tac-Toe

level 0 -- Computer is totally dumb.


level 1 -- Computer is smarter (original version).


level 2 -- You MAY win (only when you start).


level 3 -- Computer NEVER lose's.

How to use LinkedIn to fullest..

Here is how to use LinkedIn (networking Site for professional's) at your disposal.
How to exploit it and make most of it ...   Its Linkedin BootCamp... that's what it is called.

The Worst Questions To Ask During A Job Interview

Following are some question which are worst question if you ask to interviewer.Its important to answer correctly but its also important "not" to ask such question. I may say these are Taboo.

So here they are.

1] "What are your policies surrounding benefits and vacation time?"


2] Can you tell me about your company?

3] Will i have to work overtime?

4] Why did the last person leave?

5] "Do you think I'd be a good fit?" OR "What reservations do you have about me?"

 6] Will i have to travel or relocate?

7] So whats the Salary for this job?

8] How long do you think this interview will take? I have to be somewhere afterwards..

9] How long does it take to get promoted?

And the last one but important...

Saying I dont have any Question to ask at the end of the interview....

 

 

It's official - Mobile radiation is dangerous

The speculative fears of mobile phones being a danger to health in the long run, seem to be coming true. A latest government study talks about the harmful effects of not just using mobile phones but also the radiation from mobile phone towers. 

A Times of India report states that - 

Radiation from mobile phones and towers poses serious health risks, including loss of memory, lack of concentration, disturbance in the digestive system and sleep disturbances, according to an inter-ministerial committee formed by the ministry of communications and information technology to study the hazards posed by mobile phones.

One India also reported that the damages may not be lethal for humans, but they worse for birds and insects as well. The committee has attributed the radiation effects to the disappearance of butterflies, bees, insects and sparrows. 

The government report also says that  mobile towers should not be installed near high density residential areas, schools, playgrounds and hospitals. "The localized SAR value as per the Indian guidelines standard is 2 watt per kg, averaged over a six minute period and using a 10 gram average mass. With higher SAR values of mobile handsets the public could potentially receive much higher radiofrequency exposure. We have recommended that SAR levels to be lowered down to 1.6 watt/kg, as prescribed by the Federal Communication Commission of US," said a member.

The eight-member committee, which included representatives from the health ministry, department of biotechnology and member secretary, DoT, has recommended that mobile phones not adhering to standard levels of specific absorption rate (SAR) - a measure of the amount of radiofrequency energy absorbed by the body while using a phone -- should be barred.

Member scientist, ICMR R S Sharma said that compared to Europeans, Indian cellphone users are more at risk for adverse affect of radiation due the country's hot tropical climate, low body mass index, and low fat content. "We have recommended amendment in the Indian Telegraph Act 1885 and rules so that only mobile handsets satisfying radiation standards should be permitted in the country," he said.

In another report, citizens are scared of the radiation from mobile tower in Andheri. 

In the past five years, more than 15 housewives living in Sher-e-Punjab colony in Andheri (East) have suffered from various forms of cancer. Although there is no proof of a direct link, residents are drawing parallels between the appearance of the first cancer case and the installation of the first mobile phone tower in the society nearly seven years ago. Based on a Hindustan Times report.

Neha Kumar, who has been studying the biological effects of mobile phone towers, said, “All these women don’t have any family history of cancer. Plus, all of them are within a certain radius of those mobile towers. All this is not a coincidence.”

So how do we minimise the damage in view of such grave consequences. The report adds -   

“While talking on mobile, keep calls short or send a text message (SMS). This advice should be especially relevant keeping in mind the children, adolescents and pregnant women” said the report.  The advice should be printed in the user manual by handset manufacturers.  

“Whenever possible, use cellphone when the signal quality is good. People having active medical implants should keep their cellphone at least 30 cm away from the implant” said the report.

Many a times we find ourselves complaining of bad network in certain areas. Mobile companies raise towers at every nook and corner to beat each other at network coverage. Are we compromising our health for better mobile connections?

You might also like"How to use Linked to Fullest"