Loading the fixed length file data in the table using execute SQL task in SSIS

 First of all we will dump the data into the staging table then we need to use a substring to extract the data from the row and load it into the main table.

Let’s see how we will load this file.

Below is the file  

Mapping of this file

Name: 0-19

Mobile: 19-34

Address: 34-60

 Below is the stage table.

create table Emp_Stg ( id int identity(1,1) primary key, data varchar(max) )

 First of all we will load the file in the staging table.

Loading the file into the stage 

Below is the simple configuration in the flat file connection.

 

All data we are getting in one column. Like below 

Taking the OLEDB destination. 

Once we load the data into the staging table. Using the Execute SQL task we can load the data into the main table

Taking Execute sql task and loading the data into the main table.

  

Below is the script

insert into Emp(name,mob,Emp_Add) Select SUBSTRING(data,1,19) as Name, SUBSTRING(data,20,15) as Mobile, SUBSTRING(data,35,25) as Emp_Address from Emp_Stg

  

Now the package is ready to run.

Before running the package. Records in the table. 

Running the package. 

Package executed successfully.

Now see the records in the table.

 

Data loaded successfully in the main table.