TechBrothersIT is the blog spot and a video (Youtube) Channel to learn and share Information, scenarios, real time examples about SQL Server, Transact-SQL (TSQL), SQL Server Database Administration (SQL DBA), Business Intelligence (BI), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Data Warehouse (DWH) Concepts, Microsoft Dynamics AX, Microsoft Dynamics Lifecycle Services and all other different Microsoft Technologies.
SSIS - How to Load Fixed Width Text File to SQL Server Table By Using SSIS Package
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.
Let's start with step by step approach.
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
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.
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