Retrieving Master Child Record From Flat file

Introduction

One of my friend ask me write an article related to retrieving Master Child(Transaction) records from Flat File source.

It is a concept of Primary Key and Foreign Key. Here in this article we are trying to learn it. Here I am not going to describe related to Primary key and Foreign key relation.

Simple Case Scenario

Here we have a Flat file which contains the sales related records. Here is the sample of that

Saleno              Date                   ItemCode            Quantity            Rate                  Amount

S1                      01/01/2013          I101                      10.000                200.00               2000.00

S1                      01/01/2013          I102                      5.000                  100.00               500.00

S2                      02/01/2013          I101                      11.000                200.00               2200.00                    

In the above flat file we can see the both Master and Transaction data presents and we have to separate then and store it in different table objects where primary key and foreign key relation is there.

The Master Table must contain the following attributes (Columns):

1.    Sales no

2.    Date

The transaction Table must contain the following attributes (Columns):

1.    Sales no

2.    Item Code

3.    Quantity

4.    Rate

5.    Amount

To solve this problem we are taking the help of surrogated key concept.

Solution of this Case

Step-1 [ Create the Destination Table object for Master and Child Records ]

-- Master Table

IF OBJECT_ID(N'dbo.TBL_MASTER', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[TBL_MASTER];

   END

GO

CREATE TABLE [dbo].[TBL_MASTER]

       (IDNO     INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,

        SALENO   VARCHAR(50) NOT NULL,

        SALEDT   DATETIME);

       

-- Child Table

IF OBJECT_ID(N'dbo.TBL_CHILD', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[TBL_CHILD];

   END

GO

CREATE TABLE [dbo].[TBL_CHILD]

       (SALENO   VARCHAR(50)   NOT NULL,

        IDNO     INT           NOT NULL,

        ITEMCD   VARCHAR(50)   NOT NULL,

        QTY      DECIMAL(20,3) NOT NULL,

        RATE     DECIMAL(20,2) NOT NULL,

        AMOUNT   DECIMAL(20,2) NOT NULL);

GO

-- Setting the Foreign Key Relation

ALTER TABLE  [dbo].[TBL_CHILD]

ADD CONSTRAINT [FK_TBL_CHILD_IDNO] 

FOREIGN KEY (IDNO) REFERENCES[dbo].[TBL_MASTER](IDNO);

GO   

Step-2 [ SSIS Package Control Flow ]

Step-3 [ Configuring Data Flow Task ]

Load Master Data Flow Tasks Configuration

Load Child Data Flow Tasks Configuration

Step-4 [ Run the package and Analyze it ]

Hope you like it.