Master Child Table from Flat file by using ADF Data Flow

Introduction

In this blog post, we are trying to split a flat file and put the value in two different table of Azure SQL Server. I think if we have the knowledge in SSIS, using data flow is very easy. By using data flow, we got the ETL solution as we can use different transformation to process data and finally store it in destination.

Hope it will be interesting.

Case scenario

We have a flat file contains data. We need to segregate records from it like master child table.

 


Destination ( Azure SQL Table)

 


 

ADF Solution

We have a single Pipeline which has one Activity named Mapping Data Flow Activity which just calls our Data Flow.

 

Data Flows Structure

 


 

First data flow we are using for our Master table. It just store the distinct records in our master table named [Customer]

Second data flow we are using for Transaction table named [SalesOrder]

 

Master Table Data Flow

 

Source

 

 

Select Transform

 

For [Customer] table we need only [CustomerID] and [CustomerName] columns. Others columns need to be removed as in next step we are Going to Aggregate Transform.

 

Here we are taking [Qty] as extra columns as in Aggregate trans form we going to Group By the [CustomerID] and [CustomerName] and SUM() of [Qty] as Aggregate needs a Aggregate function in this transform. We are not going to Sink [Qty] in our destination.

 

Aggregate Transform

 

 

Sink

 

 

 

Transaction Table Data Flow

It is the same concept as master. We just connect source and select desired columns and sink.

 

Output

We run this Data Flow from our Pipeline using Mapping Data Flow Activity

 



 

Hope you like it.

 


Comments

Post a Comment

Popular Posts

Triggering Pipeline in ADF

Image
Introduction Pipeline is ready and we already publish it, now we need to trigger our pipeline in a schedule interval. For SSIS Developer, we have SQL Server Agent job to schedule and run the SSIS Project or Package.   Scheduling Jobs in SQL agent have some limitation. We have to logically check the SSIS Package execution is completed or not before proceeding to next run of same or dependent project. It also has some flexibility by using Steps. Suppose we need to run a SQL Script on Step-1, then SSIS package on Step-2. We are trying to say that dependent activity can run step by step. In Azure ADF triggering, we have some options that we not find in SQL Agent Job, so both have some advantage and Limitation also. In this blog post we are going to discuss ADF Add Trigger options. Hope it will be interesting.   Where we get the Add Trigger     When we open the Pipeline we can get the Add Trigger button beside the Debug button. After clicking this butt...
Read more

Working with Spark – Spark RDD

Image
Introduction In my previous blog post, we are discussing about Hadoop Map Reduce. Now in this blog post we are going to discuss about Spark Fundamentals concept like RDD. RDD stands for  Resilient Distributed  Dateset , these are the elements that run and operate on multiple nodes to do parallel processing on a cluster.  Hope it will be interesting. What is RDD According to Apache Spark documentation -  " Spark revolves around the concept of a resilient distributed dataset (RDD), which is a fault-tolerant collection of elements that can be operated on in parallel. There are two ways to create RDDs: parallelizing an existing collection in your driver program, or referencing a dataset in an external storage system, such as a shared filesystem, HDFS, HBase, or any data source offering a Hadoop InputFormat ". RDDs are immutable elements, which means once we create an RDD we cannot change it. RDDs are fault tolerant as well, hence in case of any failure, they ...
Read more