How to import Text file data into SQL Server Table

Good Afternoon friends, today I am going to walk you through with the steps to import text file data into SQL Server table. For that purpose, I have taken screenshot of each step so that you can understand in well manner. Lets create a text file with two columns Eid and Name and insert some data into this file.Please see the below image for your reference.

Now we will create a table named "Emp" in which table we want to import the data from the text file.You can refer the below screenshot for creating the table.

 Now we will open a new project and named it "TextImport" and click on "OK" button.

As soon as we click on "OK" button the below mentioned screen will be appeared, Now we will select the "Data Flow Task" from the tool bar and drag it and place it in the Control flow area and rename it as "Text data import".

Now, we will double click on "data flow task" that will bring us on "Data flow task" region, where we will choose "Flat file source" as we are going to import data from a text file(flat file). See below screenshot for your reference.

Right click on Flat file source and click on "Edit" menu as shown in the below screenshot.

As soon as we click on "Edit" menu, below screen will appear in front of us, Now we will click on "New" button to create a connection with the flat file(text file).

Clicking on "New" button will bring us on a new screen as shown in the below screenshot.Now we have to provide name to the connection manager, I have given "textfileconnection" name to the connection mannager(as encircled by blue color). Once we give the name to the connection manager, we will click on browse button and choose the text file which we want to import into the database table. I have encircled the path of the text file with red color.

At the bottom of the above screen there is a check box with label "Column names in the first data row", we will check the check box as our file contains the first row as column names.
To check the data contained by the text file, we will select the "Columns" tab as shown in left side of the below screenshot. Now, we are done with the settings of connection manager so will click on "OK" button.

Our source is configured now , we need to choose a destination, as we are going to import data into SQL Server table hence our destination task would be OLEDB task. We will select the OLEDB destination task from the tool bar and drag it and put it into the Data flow area as shown in the below screenshot.

After placing the OLEDB destination task into the Data flow area, we will right click on "OLEDB destination" task and click on edit menu that will bring up a new screen as shown in the below image.

Click on "New" button which is shown in the above image that will bring up further a new screen. Please refer below screenshot for your reference. We will put the server name in the server name text field in my case I have put ".\SQL2008". Now will select the intended database where we want to import the data from text file. In my case database name is "ETL".

We will click on "OK" button that will bring up below mentioned screen. We will further click on "OK" button on the below screen.

As soon as we click on "OK" button, it will bring us on below mentioned screen.

Now we will select table name from the combo list in  which we want to import the data from the text file.In my case table name is "Emp". In the above screen there is a tab named "Mappings" at the left side. we will select "Mappings" tab to check the mapping of the columns of text file and the database table. It will show the mappings as shown in the below image.

Since the name of the columns of the text file and the name of the columns in the table are same, SQL Server automatically maps the columns. In case your text file columns names are different from columns names of table, we can manually map them by dragging one column towards to the column you want to map and drop it on it.Now will click on "OK" button. Now we are done with our source and destination tasks. We will connect the green arrow of the Flat file source to the OLEDB destination as shown in the below image and Run this package using "F5" key. If both tasks filled with green color after running the package, it means our package is successful.

Now we can check the SQL table if the data has been imported successfully or not.

I hope you will like this article. Please feel free to ask as many question as you have.