Prerequisite

Content of Flat file (Input.txt)

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] [int] NOT NULL
  4. )
  5. CREATE TABLE [dbo].[Detail]
  6. (
  7. [id] [varchar](50) NOT NULL,[amount] [int] NOT 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.

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