Split source table into Parent Child table in SSIS

Home / Blog / Split source table into Parent Child table in SSIS

Introduction

While creating ETL package we may face some situation to split a single data into two tables with Parent Child relationship. In this article we are going to see how to achieve this scenario with efficient approach using SQL server Integration Service [SSIS].

Objective

Let’s consider, we have customer detail information in a single table, called Main Table. I would like to split this information into two separate tables, one is for storing customer basic information and another is for Address. To relate between these two tables Address table is have foreign key of customer table.

In addition to this source is having two different types of address [Office and Residential address] in customer detail table, I will split these addresses into two separate rows in CustomerAddress table. To identify the address type I have added “AddressType” column in Address table.

Expected result Explanation

For this demonstration we are going to use three tables

  1. CustomerDetails [Purpose :This is a Main table which having Consolidated Customer data] This table we are going to use as source]
  2. Customer [Purpose :Used as destination table to store customer detail except address]
  3. CustomerAddress [Purpose :Used as destination table to store only customer address]

Created the relationship between Customer and CustomerAddress table using CID column.

1

In the above Image 1.1 we have three table.

In the first table (Point 1) we have customer basic information, Office Address and Residence Address in a single row.

After executing our SSIS package, single row information will split into two separate table. We will get Customers basic information into customer table [Point 2] and Office & residential address in to CustomerAddress table (Office & residential addresses are in separate row as show in Point 3, By using AddressType column we can identify the address type)

Database structure

Below are the table structures which we have used in our scenario. (Refer Image 2.1)  Note:    Use Attached Script file to create these tables

2

Steps to follow

Step 1   : First step to pull the customer basic information from CustomerDetails (Source) table to Customer (Destination) table, including primary key of source table.

(Customer basic information are CustomerID, FirstName, LastName , IsActiveCustomer, LastModifiedDate. Rest of the columns are Address related columns, leave those unchecked.)

  1. Create new Integration Services Project in BIDS
  2. Create OLEDB Connection to connect SQL Server database.
  3. Add Dataflow task on to the package and add OLEDB Source into it.

Configure the OLEDB source Selecting only CustomerID, FirstName, LastName, IsActiveCustomer, LastModifiedDate from CustomerDetail Table columns. (As shown in below Image 3.0 A)

  1. Add OLEDB Destination and configure it to pointing to connect Customer Table. Map the above columns to Customer table columns.
  2. All set interface of Data flow task will look like below Image 3.0 B

3

Step 2   : Next, we are going to go ahead and setup the Customer Office Address related information from CustomerDetails table to CustomerAddress table.

4

  1. Add second Data flow task to the package and add two OLEDB Sources into it as shown in the above image 3.1 [Point number 1 and 2].
  2. In the first OLEDB source select CustomerID ,OStreet, OCity, OState, OZip columns from CustomerDetail Table. (Refer Image 3.2)
    Note : Here we are selecting customerID column also because we are going to use this only for mapping not for inserting data in to CustomerAddress table.
  1. In the second OLEDB source select CID and CustomerID from Customer table. Note:  Here we are going use CustomerID column for mapping with CustomerDetail table CustomerID column. (Refer Image 3.2)

5

  1. Add two Sort transformations to the Dataflow task and connect both the OLEDB sources as shown in the above Image 3.1 [point number 3 and 4]. In both the sort transformations sort the data using CustomerID column.
    Note: Here we are going to use “Merge Join” transformation to map both Customer and CustomerDetail table using customerID. “Merge Join” will accept records in sorted manner therefore we have added 2 Sort transformations.
  1. Add Merge join Transformation to the dataflow task as shown in the above Image 3.1 (Point Number 5)
    1. Configure Merge Join to left outer join as CustomerID as common field of both the tables.
    2. Here we are going to select all the Office address data from CustomerAddress table and matching CID from customer table
    3. Select the OStreet,OCity,OState,OZip column from CustomerDetail table (Except CustomerID) and select CID column from Customer Table (Except CustomerID) as shown in the below Image 3.3.

6

  1. Add Derived column Transformation dataflow component as show in the above image 3.1 (Point Number 6)

Here in this example we are splitting single row into two separate row, one row is for Office Address and another is for Residential address of the customer. To identify the Address type we have added additional “AddressType” column.

Add new column in Derived Column transformation. Write “Office” in the Expression, because first we are copying office address as shown in the below Image 3.4

9

  1. As shown in the above Image 3.1 (Point Number 7) Add OLEDB Destination to connect to customerAddress table and map CID,OStreet, OCity, OState, OZip, AddressType columns to customerAddress table columns.

Note: Here we are inserting primary key of Customer table (CID Column) to CustomerAddress table as a foreign key to relate the information

Step 3   : Next, we are going to go ahead and setup the Customer Residential Address related information from CustomerDetails table to CustomerAddress table.

Repeat the Step 2 for Residential Address, here only two changes are to be made

  1. Copy CustomerID ,RStreet, RCity, RState, RZip columns from CustomerDetail table in OLEDB Source [Reference Image 3.2 for Office address]
  2. In derived column’s expression write “Residential” in the Expression. [Reference Image 3.4 for Office address]

Step 4   : Execute the package to get expected result

7

8

Now the package executed successfully and we can see the data transfer between the source and destination (Refer Image 4.2)

Conclusion

We created an SSIS package to accomplish our goal of splitting and loading up the consolidated data from Main table into two different child tables.

Leave a Reply

Your email address will not be published. Required fields are marked *