Tasks for Hybrid Data Integration with Error Handling

Scenario
A retail company needs to move sales data from an on-premises SQL Server to Azure Blob Storage for backup, with error handling to
retry failed transfers and log issues to an Azure SQL Database table (ErrorLog).

Execution
Step Task Description Expected Outcome
s
1 Set Up Azure Data - Log in to the Azure portal and create or use an existing Data A functional ADF instance with a
Factory and Factory instance (e.g., RetailADFHybridBackup). Self-hosted IR configured for
Integration Runtime - Install a Self-hosted Integration Runtime (IR) on an on- hybrid data access.
premises machine:
- Download and configure the IR from the ADF UI.
- Register the IR with a key from the Azure portal.
- Ensure connectivity to the on-premises SQL Server.
2 Create Linked - Create a linked service for on-premises SQL Server: Linked services for on-premises
Services - Name: OnPremSQLLinkedService. SQL, Blob Storage, and Azure SQL,
- Use the Self-hosted IR. successfully tested.
- Specify SQL authentication and test connectivity (e.g., to
SalesDB).
- Create a linked service for Azure Blob Storage:
- Name: BlobStorageLinkedService.
- Use Managed Identity or storage account key.
- Test with a storage account (e.g., retailstorageprod).
- Create a linked service for Azure SQL Database (for error
logging):
 - Name: AzureSQLLinkedService.
- Use SQL authentication or Managed Identity.
- Test with a database (e.g., RetailDB).
3 Create Datasets and - Create a source dataset for SQL Server: Datasets for source, sink, and
Error Log Table - Name: OnPremSalesDataset. error logging, with an error log
- Link to OnPremSQLLinkedService. table created in Azure SQL.
- Reference a table (e.g., Sales).
- Create a sink dataset for Blob Storage:
- Name: BackupSalesCSVDataset.
- Link to BlobStorageLinkedService.
- Set path to sales-backup/yyyy/MM/dd/.
- Configure CSV format.
- Create a dataset for the error log:
- Name: ErrorLogDataset.
- Link to AzureSQLLinkedService.
- Create a table ErrorLog in Azure SQL with columns: PipelineRunID,
ErrorMessage, Timestamp.
4 Build the Hybrid - Create a pipeline named HybridBackupPipeline. A pipeline that copies data from
Backup Pipeline - Add a Copy Data activity: on-premises SQL to Blob Storage,
- Name: CopySalesToBlob. with error logging on failure.
- Source: OnPremSalesDataset.
- Sink: BackupSalesCSVDataset.
- Enable retry policy: 3 retries, 30-second interval.
- Add an Execute SQL activity for error logging:
- Name: LogError.
- Link to ErrorLogDataset.
- Query: INSERT INTO ErrorLog (PipelineRunID, ErrorMessage, Timestamp)
VALUES (@pipeline().RunId, @activity('CopySalesToBlob').error.message,
@utcnow()).
- Set to run on failure of CopySalesToBlob.
5 Test the Pipeline - Ensure sample data exists in the on-premises SQL Server Sales Successful data copy with retries
 table. on transient failures, and errors
- Manually trigger the pipeline in the ADF Author tab. logged to ErrorLog.
- Simulate a failure (e.g., temporarily disable the Self-hosted IR)
to test retry and error logging.
- Verify that successful runs copy data to
sales-backup/yyyy/MM/dd/ and failures log to ErrorLog.
- Check pipeline run details in the ADF Monitor tab.
6 Configure a Schedule - Create a Schedule Trigger named DailyHybridBackupTrigger. Automated daily data backup with
Trigger - Set to run daily at 2 AM UTC (7:30 AM IST). error handling and logging.
- Associate with HybridBackupPipeline.
- Activate and monitor the first scheduled run.
7 Set Up Monitoring - Configure Azure Monitor to track pipeline runs and error Reliable monitoring with alerts for
and Alerts logging activity. pipeline failures or logged errors.
- Create an alert rule to notify (e.g., via email) if the pipeline
fails or errors are logged.
- Review run history for consistency.
8 Validate and - Validate that data is copied to Blob Storage and errors are A validated hybrid pipeline with
Document logged in ErrorLog during failures. error handling, documented for
- Test with different SQL table data to confirm robustness. reusability.
- Document the pipeline, including IR setup, retry logic, and
error logging, for reference or portfolio use.

Expected Outcome
 An ADF pipeline that moves sales data from an on-premises SQL Server to Blob Storage with retry logic and error logging to
Azure SQL.
 Automated daily execution with monitoring and error handling.