Scribd
Upload
2 views

Full_Load -V2

The document outlines a process for copying multiple tables from an Azure SQL database to ADLS Gen2, including handling scenarios like checking table availability and maintaining a metadata table. It details the use of Azure Data Factory components such as datasets, linked services, and activities like lookup and foreach to manage data flow. Additionally, it provides SQL scripts for table creation and data insertion, along with steps for setting up the necessary activities in Azure Data Factory.

Uploaded by

KONDURU KRISHNA
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views

Full_Load -V2

The document outlines a process for copying multiple tables from an Azure SQL database to ADLS Gen2, including handling scenarios like checking table availability and maintaining a metadata table. It details the use of Azure Data Factory components such as datasets, linked services, and activities like lookup and foreach to manage data flow. Additionally, it provides SQL scripts for table creation and data insertion, along with steps for setting up the necessary activities in Azure Data Factory.

Uploaded by

KONDURU KRISHNA
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

Full Load:

Scenarios:
1. How to copy multiple tables from azure SQL database to ADLS gen2.
2. How to avoid running copy activity if table not available in source side
3. How to create & maintain metadata table
4.

Estimations:
1. Azure data factory
- Auto-Resolve IR or Azure IR
- 4 datasets
- 3 linked services(source, destination, keyvault)
- Pipeline
2. Look up activity, Foreach activity (Get metadata activity, if activity (True
(copy activity) or False (wait activity)))
3. Azure SQL Server - Source
4. Storage accounts – ADLSgen2 - Destination
5. Key vault
Imp points:
1. In ADF- there is a relationship b/w previous activity and coming activity
2. previous activity producing some output , that ouput help to the coming activity
as a input
3. Look up activity always talk about "value" manner
4. foreach activity always talk about "item" manner
5. sql server point of view talk about "row" manner
6. Dataset parameters - For handling bulk of tables+ each and every table have the
own schema name
and table name as well as I want to maintain my own nameing convention, once
the data is succesfully
copied from azure sql to adlsgen2 under bronze container

7. Dont create dataset parameters for look up activity becoz purpose of lookup
activity here to get

only metadata information

8. Dataset parameters applicable for getmetadta activity and copy activity


9. Copy activity ->if activity ->foreach activity
Lab:
Step1:
Sql scripts:
1. Table creations
 create table product(pid int, pname varchar(50))
 create table sells(sellsid int, storename varchar(50))
 create table transactions(tid int, tamount bigint)
 create table cust(cid int, clocation varchar(50))
 create table emp(empid int, empname varchar(50))
2. Insert data into tables
 insert into product values(111,'Laptop'),(222,'Mobile')
 insert into sells values(1,'Flipkart'),(222,'amazon')
 insert into transactions values(101,1000),(202,2000)
 insert into cust values(1000,'Banglore'),(2000,'hyderabad')
 insert into emp values(888,'rama'),(999,'krishna')
3. create table metadata(schemaname varchar(50),tablename varchar(50),
blobcontainer varchar(50), isdisable int)
4.insert into metadata values('dbo','product','productoutput',0),
('dbo','sells','sellsoutput',0),
('dbo','transactions','transactionsoutput',0),
('dbo','cust','custoutput',0),
('dbo','emp','empoutput',0),
('dbo', 'orders', 'ordersoutput',1)

Step2:

Linked services:

Datasets

Lookup activity
1.lookup
-dataset, no need to select any table
2. foreach
get metadata.
key: exist.
-dataset, no need to select any table
- Step1: create dataset parameters
- Step2: go to connection under dataset, click on table, click on enter manually,
double click on adds dynamic content.

- Step3: go to pipeline, click on get metadata activity, you will see dataset
parameters
- we need to pass the values, but we are under foreach activity, always foreach
understand
item only
- pass the dynamic expression for values
- give the lookup activity output left side values
Dataset parameters for get metadata activity.
@equals(activity('Get Metadata1').output.exists,true)

Copy activity:
Source:
Sink:

You might also like