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.
