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.
