Split Flat FIle data and Load into Parent and Child SQL tables

Let’s say we have Denormalized data (coming from a file) that needs to be imported into parent-child tables. 
for example.
the source flat file looks like this.

the file has tab delimiter between the columns.

Now let’s create the parent table and child table to load this source file.

ParentTable


CREATE TABLE [dbo].[ParentTable](
[ParentId] [int] IDENTITY(1,1) NOT NULL,
[Account] [varchar](12) NULL,
[Name] [varchar](50) NULL,
 CONSTRAINT [PK_parentCon] PRIMARY KEY CLUSTERED 
(
[ParentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ChildTable

CREATE TABLE [dbo].[ChildTable](

[ChildId] [int] IDENTITY(1,1) NOT NULL,

[ParentId] [int] NULL,

[Street] [varchar](50) NULL,

[City] [varchar](12) NULL,

[Zip] [varchar](12) NULL,

[Email] [varchar](50) NULL,

 CONSTRAINT [PK_childCon] PRIMARY KEY CLUSTERED 

(

[ChildId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Now we have a DDL statements for ChildTable and ParentTable, let’s design a SSIS package.

SSIS package with name ParentChild.dtsx.

and 

above image shows two Data flow task in Control flow.

one is Parent and another is Child

the connection managers we will be using are given below.

I have already shown the source flat file format.

you can embed into the flat file connection manager by creating flat file with the data.

how we use Flat File connection manager.

After having set up flat file connection manager, let’s go for Data flow task we have put on our Control Flow task.

which are Parent and Child

let’s look at Parent DFT, what we will be having in it.

we will have Flat file source, Sort transformation, and OLE DB destination.

Flat File Transformation Step

1.

2.

Let’s have Steps for Sort Transformation.

1.

Let’s have steps for OLE DB Destination.

1.

2.

let’s look at Child DFT, what we will be having in it.
we will have Flat file source, LookUp transformation, and OLE DB destination.

let’s see what we will be using in Flat File Source Transformation

steps are given below.

1.

2.

we have now source as a flat file.

now let’s take LookUp Transformation.

steps are given below.

1.

2.

3.

now that we have LookUp transformation set it up, let’s have the final transformation which is OLE DB Destination  Transformation to Load Data to Child Table.

while connecting LookUp Transformation to OLE DB Destination transformation use LookUp Match Output as a result from LookUp Transformation to Input for OLE DB Transformation.

let’s have steps here for OLE DB Destination Transformation.

1.

2.

Now we have everything required input and destination table with us.

let’s run the package.

1.

2.

3.

4.

You can see the ParentTable and ChildTable with data.

Good Luck with your Parent and Child table loading.