Jul 14, 2014

Import a Flat file with Header and Detail Rows in SSIS

In this post, I am going to demonstrate Step by Step Example of importing a Flat File which contains both Header and Detail Records with different formats in the SQL Server Database header & detail tables respectively using SSIS.

  • An Input File Input.txt with header and detail records
  • Header & Detail Tables in the database
Content of Flat file (Input.txt)

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.

Data Flow Task

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

Flat File Source

Step 3: Configure Flat File source.
  • Select file name (Input.txt)
  • Set Format as "Ragged Right"
Configure Flat File Source

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 below

Input Columns

Step 6: Configure Output under Inputs and Outputs tab like below as per Header & Details specifications.

Configure Output

Step 7: Click on Edit Script and write following code in the script window under Input0_ProcessInputRow method
public override void Input0_ProcessInputRow(Input0Buffer Row)
    if (Row.Line.Substring(0, 1) == "H")
        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.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 respectively.

Add OLE DB Destintation

Step 9: Configure OLE DB destination for Header & Detail like below

Configure OLE DB Destintation
Configure OLE DB Destintation

Step 10: Now let's execute the package

Package Execution

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

    Choose :
  • OR
  • To comment
No comments:
Write Comments

Probabilmente come studente universitario ti troverai a scrivere una o più tesi per il tuo analogo esame di fine carriera. Ma di cosa si tratta in particolare? Scopriamo quali sono i pro dei tool per riscrivere testo che troverai online. Per strumento di parafrasi si intende un tool online che si occupa di parafrasare in maniera immediata frasi oppure interi paragrafi. Questa attività si svolgerà interamente su internet e non lascerà alcuna traccia di plagio