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

Flat file Header

Script for Header & Detail Tables

  1. CREATE TABLE [dbo].[Header]  
  2. (  
  3.    [id] [varchar](50) NOT NULL,[amount] [intNOT NULL  
  4. )  
  5. CREATE TABLE [dbo].[Detail]  
  6. (  
  7.    [id] [varchar](50) NOT NULL,[amount] [intNOT NULL,[product] [char](100) NOT NULL  
  8. ) 
Step 1: Add a Data Flow Task to Control Flow.

Control Flow

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

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,

Script Component

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

Input Columns

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

specifications

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

  1. public override void Input0_ProcessInputRow(Input0Buffer Row)  
  2. {  
  3.     if (Row.Line.Substring(0, 1) == "H")  
  4.     {  
  5.         HeaderBuffer.AddRow();  
  6.         HeaderBuffer.id = Row.Line.Substring(1, 1);  
  7.         HeaderBuffer.amount = Convert.ToInt16(Row.Line.Substring(2, 3));  
  8.     }  
  9.     else if (Row.Line.Substring(0, 1) == "D")  
  10.     {  
  11.         DetailBuffer.AddRow();  
  12.         DetailBuffer.id = Row.Line.Substring(1, 1);  
  13.         DetailBuffer.amount = Convert.ToInt16(Row.Line.Substring(2, 3));  
  14.         DetailBuffer.product = Row.Line.Substring(5, 2);  
  15.     }  
  16. }  
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

Step 9: Configure OLE DB destination for Header & Detail like the following,

Header

Detail

Step 10: Now let's execute the package,

package

Step 11: Package is executed successfully. Now let's check for the data in the tables.
  1. select * from Header  
  2. select * from Detail  
Output

output