Import Data From Text File To Database Table Using SSIS 2005

We first of all create a table tbl_contact in Sql server 2005. This table tbl_contact has following definition

Column Name Data Type Constraint/Description
user_id int Identity(1,1) Primary Key
name Varchar(50) Name of Person
lastname Varchar(50) Last Name of Person
city Varchar(50) City of Person

Now we have a CSV(Comma Separated Value) file. This is a text file that contain name of person, last name of person and city of person each field separated by a comma and each set{name,lastname,city} is separated by vertical bar(|).This file can have any name and its extension is .txt.
Now our functionality is import data from this CVS text file to database table tbl_contact ,so our function are 10 simple steps.
1. Start-All Programs-Microsoft SQL Server 2005-SQL Server Business Intelligence Development Studio-ok
2. File-New-Project-Integration Services Project.
Here we give Integration Services Project name and location where we are create this project and click OK.

3. Drag and Drop Data Flow Task from ToolBox to Control Flow of Package.dtsx

4. Now we switch from Control Flow to Data Flow. In Data Flow pane we drag and drop Flat File Source for our CVS Text File.

5. Now Right Click in Connection Managers Pane -select New Flat File Connection…
In this Fill Connection Manager name (can be any name no special), File name(Text file for import data) and select Header row delimiter(Here use of Comma(,))

6. Now Flat File Connection Manager Editor in left Pane , Click on Columns Tab and define Row delimiter(Here Vertical Bar{|} same as CVS File) and Column delimiter (Same as pervious Comma{,}) and click OK.

7. Now Drag and drop OLE DB Destination in Data Flow Pane from ToolBox. OLE DB Destination should be lower from Flat File Source and connect Flat Source File to OLE DB with green arrow. This green arrow represents Source to Destination of data.

8. Now Double Click on OLE DB Destination and define Connection to database which we create in sql server 2005(here is TEST)and select a table tbl_contact for insert data from CVS Text file.

9. Now left pane of OLE DB Destination Editor select mapping for column relationship between table tbl_contact and CVS Text file columns and Click OK.

10. Now Execute means press F5 and Show result in table tbl_conatct

Advertisements

2 responses to this post.

  1. Posted by Davesh sharma on May 24, 2011 at 12:27 am

    I think CSV file and text file are different..Importing from csv file is easy as compared to text file.
    Pls correct me if i m wrong and explain.
    thanks

    Reply

    • CSV File is a type of Text File.csv file is simple text file there are each field(name) seprated by comma(,).But in Sql Server 2005/2008 it facility that you can have a file that each field separated by other like vertical bar (|) etc. But we mostly use text file as CSV File to import data from file to database table. i hope this will be helpfull.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: