Import Header Detail In SQL Server

Prerequisite

An input file (Input.txt) with header and detail records.
Header & Detail Tables in the database.

Content of Flat file (Input.txt)

H1100
D1050P1
D1050P2
H2200
D2100P3
D2100P4

Flat file Header/Detail Record Specs

Script for Header & Detail Tables

CREATE TABLE [dbo].[Header]
(
[id] [varchar](50) NOT NULL,[amount] [int] NOT NULL
)
CREATE TABLE [dbo].[Detail]
(
[id] [varchar](50) NOT NULL,[amount] [int] NOT NULL,[product] [char](100) NOT NULL
)

Step 1: Add a Data Flow Task to Control Flow.

Step 2: Add a Flat File Source to the Data Flow.

Step 3: Configure Flat File source.

Select file name (Input.txt)
Set Format as "Ragged right"

Ragged Right

Step 4: Add Script Component from Data Flow Transformations and connect Flat File Source to it,

Step 5: Double click on Script component and Configure the Input Columns like the following,

Step 6: Configure Output under Inputs and Outputs tab like the following as per Header & Detail specifications.

Step 7: Click on Edit Script and write the following code in the script window underInput0_ProcessInputRow method:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if (Row.Line.Substring(0, 1) == "H")
{
HeaderBuffer.AddRow();
HeaderBuffer.id = Row.Line.Substring(1, 1);
HeaderBuffer.amount = Convert.ToInt16(Row.Line.Substring(2, 3));
}
else if (Row.Line.Substring(0, 1) == "D")
{
DetailBuffer.AddRow();
DetailBuffer.id = Row.Line.Substring(1, 1);
DetailBuffer.amount = Convert.ToInt16(Row.Line.Substring(2, 3));
DetailBuffer.product = Row.Line.Substring(5, 2);
}
}

Step 8: Add two OLE DB destination for header and detail and connect Header and Detail output of Script Component to each destination.

OLE DB