Load Tab Delimited File to SQL Server Table Using SSIS Package

This article we will learn how to load the Tab delimited file in sql server table using SSIS package. Below is the file.

 

This is the tab delimited file. We will load this file in sql table.

Creating a table in sql server database.

CREATE table [Test].[dbo].EMP   (   ID INT Primary Key IDENTITY(1,1),   Name varchar(50),   empAdd varchar(50),   Mobile varchar(10)   )

Now I am opening SSDT

Taking Data flow task

 

Double click on the Data Flow task

Taking Source as Flat File Source in data flow.

 

Double Click on the Flat file source

 

Create a new connection. Click on the new button.

 

Click on the browse and select the file which we want to load.

 

Click on the Columns here we will select the Row and Column delimited.

Row delimiter

 

Column delimiter

 

Below is the delimiter

Delimiter	Description
Tab{t}	Tab delimiter
{CR}{LF}	Delimited by a carriage return-line feed combination
{CR}	Delimited by a carriage return.
{LF}	Delimited by a line feed
Semicolon{;}	Semicolon
Colon{:}	Colon
Comma{,}	Comma
Vertical Bar{|}	Vertical Bar

Given file Row delimiter is {CL}{LF} and column delimiter is tab{t}.

 

Select the Advanced tab. Here we can see the columns and we can change the data type

 

If we want to add a new column or delete an existing column we can do from here.

Very interesting about Suggest Type button.

 

Suggest Type button

See the above image when we go the advance tab we will get this button.

  

In this window we can change the column data type, name and size. If by mistake we did some wrong data type mapping then it will the cause of the issue. If we have less column we can check it but if the number of columns more it will be bit default.  If we click on the Suggest Type button it will assing the data type which in the source.

See the example.

Source data type for the all column is string but here I have change as int and float.

 

But when I man clicking on the Suggest Type button it will reset the data type as source.

 

Click ok and see.

 

Now select preview button so see the file data.

 

Click ok.

Now we need to take the destination to load data into the table.

 

Creating the connection.

  

Go to the Mappings tab and map the source and destination.

 

To handle the error go to the Error Output table and configure the error redirection.

 

Here we can configure if the package is failed. Either we can fail the package or we can ignore the error or we can redirect the error in some other destination.

Now click ok. Package is ready to execute.

 

Package executed successfully. Now we check the data in database table.

 

Here I am taking another example in this example we are using Vertical Bar {|} as Row delimiter and tab {t} as column delimiter.

See the file

 

Now we will load this file into database table.

Taking data flow task

 

Double clicking on this task

Taking source as flat file source

 

Doing the configuration

 

Now select column tab

By default it will show below

 

Here we need to set the row and column delimiters

And click on refresh then we able to see the data.

 

Click ok.

Now we need to take destination to load the data in database table.

 

Configuring the OLEDb destination and click ok.

Now I am executing the package. Package executed successfully.

 

See the data in database table.