Importing data from CSV To SQL Server Table
Articles,  Blog

Importing data from CSV To SQL Server Table

Hi friends I am Ved Prakash and today I am going to show you demo to import data from
CSV file and insert into SQL Server table using SSIS So I’ll open Microsoft Visual
Studio that is IDE for SSIS development I’ll go to file the new then project
and I will select integration services project. There are so many other type of
projects you can select here . CSV to SQL is okay here I’ll select data flow task if I
double click I’ll come here and I can select my
source so as I want to select data from CSV file that is flat file and select
flat file source so you can see the red mark here because there is no source
selected so I have one CSV file pre-populated those who don’t know how
to create CSV file can go to excel and insert data like this then they can say
save as and select your destination there they can select CSV format as I
have already created it I’ll press cancel or close I’ll go back to SQL Server integration services
development IDE that at Microsoft Visual Studio now I click on this flat file
source and here I can create a new connection to that file I can give any
name here I can say new connection it is basically a description name is
pre-populated we can change it or we can keep like that only I’ll select my file from here where as
it is CSV file I will get it in CSV option now open and I can preview my
data here I can see my columns and as Row delimiter is only a Enter key the return key so it is given here CR
and LF there are so many other options you can select semicolon crlf : comma
you can choose any character to end your row and you have to give it here so that
your file connection manager will recognize where your row is ending and
column delimiter is comma so your file connection manager can understand where
your column ends so that other column can start this is the way your file
connection manager reads the CSV file so I’ll press ok again preview data here to
swing with tabular format I’ll press ok here so my file connection manager is
ready I have one connection to my flat file now my ultimate target is to send
it to sequel server table so I will choose OLEDB connection and
I’ll try to connect create one connection I will say new here to show you but I have
already one connection I’ll delete it and I’ll show how this connection is
created so I’ll say new and I write my database server name that is my laptop
itself for me I will type username password here I’ll select my database I can test my
connection so test connects and succeeded press okay okay
I can select my table here if there are many tables you’ll get many tables in
drop-down this interface will show you all the tables in the database now is
there any existing data no data now I can press ok still it is red because
there is no connection between source and destination I’ll put one connection
here still it is red because there is no mapping between the columns yes there
are one one corresponding columns with the same name it will populate and
relate those columns automatically or if the names are different I have to map
them , I press ok still my destination is not happy. It
is showing red to say that there is error in converting from Unicode to non
Unicode string datatype so if I tell you something high level so that this is
converted from DT underscore STR to DT underscore wstr so let me show how to do it. I’ll delete this connection and I will use some conversion tool that is a
data conversion component Connect this and Connect this (lower) you can connect later also.. Here I’ll select what I have to convert so I have to convert name and
Date of birth and I have to convert to DT underscore wstr DT underscore WSTR I press okay Still it is unhappy because there
is mapping with the old columns I have to map them to the converted columns copy of name copy of date of birth. many
people make them with the same name let us not do that mistake because it will
create confusion. Now press OK and looks like now it is satisfied so let us run
it before running I want to show you something there is no data in
destination now let me run it ……. okay………… Wow
it is happy very good six rows import it let us see the proof, very good it’s done
so this was a simple demo for showing how you can take data from CSV file and
insert it to SQL Server database in the table and a simple demo of
conversion of data type and in next coming videos I’ll show you some more complex Script task Till then … Thank you for watching


Leave a Reply

Your email address will not be published. Required fields are marked *