Here are some easy steps to transfer text file data into SQL server.
Firstly, we need a text file by which we want to transfer data into the database, so we have to create a text file first and save it.
Step 1: Create a Text File.
In this first step we are going to make a text file from which we want to import data into the database table and save the file into any drive with a name.
We created a text file and saved that file with DataFile name in the D: drive.
Figure 1: TextData
Step 2: Create Database
After the end of the first step, go for the second step wherein we are going to create a new database.
Now go to SQL server and create a database in SQL server. You can create a SQL server database through query or manually, it is your choice. Here we are creating a database by query.
Figure 2: Database
Step 3: Create A Database Table.
Now we can create a database table with 4 columns and 6 rows, it is optional because SQL server wizard automatically created a table when we import data.
Figure 3: CreateTable
Step 4: Import Data.
Now, start importing data
Go to database
Oct_7_database and right click on the database. After that select
Tasks, then select import data by pressing enter key.
Figure 4: SelectImport
Step 5: Welcome to Wizard.
Now after clicking on Import data option, a new window “
Welcome to SQL Server import and Export Wizard” will open. Here select “Next Button”:
Figure 5: Welcome to Wizard
Step 6: Choose a Data Source.
Here in the data source option select “
Flat File Source”.
Figure 6: FlatFile
Step 7: Select header row delimiter.
After selecting “
Flat File Source”, it will open some textboxes. In this window, select File name and browse the file where your file saved. After selecting flat file, select “
Tab{t}” in “Header row delimiter” and uncheck the “Column name in the first data row”. After that click on next button.
Figure 7: Select text file
After selecting the text file as in the following image, click next.
Figure 8: Click next
Figure 9: Click next button
Step 8: Choose a Destination.
In this section select a “
Destination”. In the destination we need to select “Microsoft OLE DB Provider for SQL Server”.
Figure 10: Select OLE DB Provider
Now after selecting ”Microsoft OLE DB provider for SQL Server” select Server name, here my server name is “MCNDESKTOP16” because this is my local server name. You are selecting your server name and now the option is “Authentication”. Here select an option where you will login your SQL Server.
If you Login Windows Authentication, select first radio button “Use Windows Authentication” and if you login with SQL Server Authentication, then select “Use SQL Server Authentication”. Here I login with SQL Server authentication, so provide the “user name” and “
password” and select the next button.
Figure 11: Authentication
Step 9: Select Source Tables and views.
After authentication process we will go to “
Select Source Tables and Views” window. In this section, wizard automatically creates a table, here the default table name is “[dbo].[DataFile]” . If you want to import data in the default table, then you can insert, otherwise you are selecting your database table.
Select -> [dbo].[Import_Data] data table.
After selecting your table, click on “
Edit Mapping” button. Here new window will open with the name “
Column Mappings”.
Here “
Append rows to the destination table” radio button is already selected and mapped. After that click ok.
Figure 12: Mapped
You can check your table preview on clicking “
Preview” Button. Now click on next button
Figure 13: Preview
Step 10: Save and run Package.
After clicking on the next button, a new windows will be visible with the name “Save and Run Package”. In this section you can save packages, then select checkbox “Save SSIS Package” and if you did not saved a package then select check box “Run Immediately”. Here we did not saved a package so select
Run immediately checkbox and click on “Next Button”.
Figure 14: Run Immediately
Step 11: Complete the Wizard.
Now we are on final stage, here a windows “Complete the Wizard” will be visible. Now click on
Finish Button.
Figure 15: Complete Wizard
Step 12: After clicking on the
“finish button” we can see the next window.
“
Performing Operation”: Here operation is in progress.
Figure 16: Progress
When all operations are successful, then your import operation completes properly and your file has been successfully imported.
Figure 17: Successful
Step 13: This is the final step of this article, now go to SQL Server and check that your data successfully inserted or not.
Figure 18: Final output