SSIS - How to Load Fixed Width Text File to SQL Server Table By Using SSIS Package

Scenario:

In my last post, I have created the Fixed Width Column Text File from SQL Server Table. In this post we will learn how to load the fixed width flat file or text file into a SQL Server Table.

Solution:

Let's start with step by step approach.

Step 1:  

If you have fixed width flat file as source that is great if not then use this post to create one by using Any data. In my case I have the Fixed Width Text file that I created in old post.

Fig 1: Fixed Width Flat File to Load to Table by using SSIS Package

Step 2:

Create an SSIS Package. Inside SSIS Package, Drag Data Flow Task to Control Flow Pane. Inside Data Flow Task, Bring Flat File Source and configure as shown below.

Fig 2: Create Flat File Connection to Load Fixed Width Text File in SSIS Package

Once you click on New, It will open below window. You have to Browse to your Source file.

In Format: I have chosen Ragged Right that means I have fixed Width Flat and Carriage Return at the end of each row. If you have Column Names in first Row , then Check the Box: Column names in the first data row as shown below

Fig 3: Browse to Location where The Fixed Width Flat file is present

Click on Columns Tab as shown below and then Click inside to put line for column length as shown below. You have to do that for all the columns. 

Fig 4: Define Column Lengths in Flat File Connection Manager

In below fig, you will click on Advance and then you can change the data type of columns as you want.

Fig 5: Choose the data Types of columns in Flat File Connection Manager

In this step if you want to remove some columns , you can un-check the box for those column/s.

Fig 6: Choose the Required Columns in Flat File Connection Manager

Hit Ok and you will see the Flat File Source is configured successfully.

Step 3:

Bring the OLE DB Destination to the Data Flow Task and connect Flat File Source to it. Once connected double click on it and configure as shown below.

Fig 7: Configure OLE DB Destination in SSIS Package to Load Fixed Width File data

Let's run our ssis Package and check the table if data is loaded successfully.

Fig 8: Fixed Width To SQL Table SSIS Package

Fig 9: Data Loaded into SQL Server Table from Fixed Width Flat File

Watch the post as video tutorial