Jeff Taylor
Principal Data Consultant
Database Consulting, LLC
Azure Data Factory (ADF)
Your New SSIS


Jeff Taylor
Principal Data Consultant
Database Consulting, LLC





Super Hero Suit



Jeff Taylor
Principal Data Consultant
Database Consulting, LLC
Azure Data Factory (ADF)
Your New SSIS

Audience Poll
Who's in the room?
- Who's running SQL Server Integration Services (SSIS) today?
- Where do your packages run? SQL Agent? SSIS Catalog (SSISDB)? Azure?
- Who has started using Azure Data Factory (ADF)?
- Who's in the cloud? Hybrid? Still on-premises?
Agenda
- What is it? - Concepts and Activities
- SSIS to ADF - What maps to what
- Building Pipelines - Parameters, Triggers, Error Handling
- Running It - Monitoring, Security, Deployment
- Cost - Large vs. Small
- Demo
ADF - What is it?
Something Sweet!
Azure Data Factory is a 'code-free' (almost) cloud-based ETL/ELT data-driven integration service AT SCALE!
Extract, Load, Transform
You can still code if you want
ADF - What is it?
Something Sweet!
- Ingest - 100+ Native connectors
- Control flow - Pipelines
- Data Flow - Transformations
- Schedule
- Monitor
ADF - What is it?
Top-level concepts
- Pipelines - managing flow
- Activities - tasks
- Datasets - source and destination data
- Linked services - connection strings
- Data Flows - no-code transformations
- Integrated Runtimes - execution - (on-premise or in cloud)
SSIS to ADF
What maps to what?
- Control Flow - Pipeline
- Data Flow Task - Copy activity or Mapping Data Flow
- Connection Manager - Linked Service
- Precedence Constraints - Dependencies (Success, Failure, Completion, Skipped)
- Foreach Loop Container - ForEach activity
SSIS to ADF
What maps to what?
- Script Task - Azure Function, Notebook or Custom activity
- Execute SQL Task - Script or Stored Procedure activity
- Project Parameters - Pipeline and Global Parameters
- Environments - Global Parameters and Azure Key Vault
- SQL Agent Job - Trigger
- SSIS Catalog (SSISDB) Reports - Monitor hub
SSIS to ADF
Lift and Shift
- Azure-SSIS Integration Runtime (IR) - Runs your existing packages in Azure
- Execute SSIS Package activity - Call a package from a pipeline
- Package Location - SSIS Catalog (SSISDB), Azure Files or package store
- Billed While Running - Start and stop the IR on a schedule
- Self-hosted IR as a Proxy - Reach on-premises data
ADF - Activities













ADF - What is it?
Something Sweet!

Something Sweet!
ADF - What is it?
Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

Something Sweet!
ADF - What is it?

ADF - Parameters
Metadata driven
- Pipeline Parameters - Passed in at run time
- Global Parameters - Shared across every pipeline in the factory
- Variables - Set and changed during the run
- Dynamic Content - Expressions that start with @
- Lookup + ForEach - One pipeline, every table
ADF - Incremental Load
Watermark pattern
- Lookup - Get the old watermark
- Lookup - Get the new watermark from the source
- Copy - Only the rows between the two
- Stored Procedure - Save the new watermark
ADF - Triggers
Your new SQL Agent
- Schedule - Wall clock, like a SQL Agent schedule
- Tumbling Window - Fixed time slices, with backfill and dependencies
- Storage Event - A file lands in Blob Storage or Data Lake
- Custom Event - Your app publishes to Event Grid
- Manual - Trigger now, or from the REST API
ADF - Error Handling
Old Method
- OnError Event Handler - Package or task level
- Precedence Constraint - The red arrow on failure
- Checkpoints - Restart from the failed task
ADF - Error Handling
New Method
- Upon Failure Path - Run a step only when an activity fails
- Retry and Timeout - Set on every activity
- Fail Activity - Stop the run with your own error message and code
- Try/Catch - Upon Failure to a logging step
- Rerun from Failed Activity - Pick up where it broke
ADF - Monitor
Where did it break?
- Monitor Hub - Pipeline, activity and trigger runs
- Rerun - From the start or from the failed activity
- Alerts - Azure Monitor alerts on failed runs
- Diagnostic Settings - Send run history to Log Analytics
- Retention - Run history is kept for 45 days
ADF - How?
What is needed...
- A Resource Group - Manage and monitor costs
- Code Repo - To store ADF source code - Azure DevOps
- Data Storage - Azure Data Lake
- Database - Serverless Azure SQL
ADF - Security
Keep secrets out
- Managed Identity - No passwords in linked services
- Azure Key Vault - Store connection strings and secrets
- Self-hosted Integration Runtime (IR) - Reach on-premises data, outbound only on port 443
- Managed Virtual Network (VNet) - Private endpoints to your data sources
ADF - Deployment
Source control first
- Git Integration - Azure DevOps or GitHub
- Collaboration Branch - main, with feature branches for changes
- Publish - Builds Azure Resource Manager (ARM) templates
- Continuous Integration and Delivery (CI/CD) - Promote Dev, Test and Prod with a pipeline
ADF - Deployment
Automate it
- Automated Publish - npm package @microsoft/azure-data-factory-utilities
- Parameters File - Different connections per environment
- Pre and Post Deployment Script - Stop triggers, deploy, start triggers
- Global Parameters - Deployed with the ARM template
ADF - SQL
Configuration - Large

ADF - Data
Size - Large


ADF - $
Cost - Large

ADF - SQL
Configuration - Small

ADF - Data
Size - Small



ADF - $
Cost - Small

What did ADF cost?
$84.88 - Small
$326.40 - Large
ADF - Demo
DEMO TIME
ADF - SQL
Configuration - DoD

What did ADF cost?

Recap
Best Practices
- Parameterize
- Source control first
- Key Vault everything
- Alert on failure
- Start small
- Watch the meter
Resources
- Azure Data Factory - https://learn.microsoft.com/azure/data-factory
- SSIS Migration - https://learn.microsoft.com/azure/data-factory/scenario-ssis-migration-overview
- Incremental Load - https://learn.microsoft.com/azure/data-factory/tutorial-incremental-copy-overview
- Error Handling - https://learn.microsoft.com/azure/data-factory/tutorial-pipeline-failure-error-handling
- Continuous Integration and Delivery (CI/CD) - https://learn.microsoft.com/azure/data-factory/continuous-integration-delivery
- Pricing - https://azure.microsoft.com/pricing/details/data-factory
Questions

Thank you!
Jeff Taylor
Thank you for attending my session today.
If you have any additional questions, please don't hesitate to reach out.



Azure Data Factory (ADF) - Your New SSIS
By reviewmydb
Azure Data Factory (ADF) - Your New SSIS
- 79