Archived Record Cleanup
Scheduled weekly flow that queries SQL for records marked as archived over 90 days ago, then deletes them one by one. Demonstrates Delete Row with a scheduled maintenance pattern.
Provided as-is, without warranty of any kind. Review and test each pattern in a non-production environment before deploying it to live automations. See our Terms.
Overview
FlowLibs - Archived Record Cleanup is a scheduled weekly flow that automatically identifies and permanently removes SQL Server records that have been in an archived state for longer than a configurable retention period. After cleanup, it sends a summary email to the designated administrator with details on how many records were deleted.
This flow demonstrates a maintenance/cleanup automation pattern — a common enterprise need for keeping databases lean by purging stale archived data on a recurring schedule.
Use Case
Organizations that soft-delete or archive records (orders, tickets, logs, form submissions) often accumulate stale data that consumes storage and slows queries. This flow automates the "hard delete after retention period" step, running weekly during off-hours to minimize impact.
Flow Architecture
Weekly Recurrence
RecurrenceFires every Sunday at 2:00 AM Eastern. Configurable schedule via the trigger settings.
Initialize Variables
Initialize variable (x6, parallel)Six parallel Initialize variable actions: varSqlServer ← flowlibs_SqlServerName env var; varSqlDatabase ← flowlibs_SqlDatabaseName env var; varSqlTable ← flowlibs_SqlTableName env var; varNotificationEmail ← flowlibs_NotificationEmailAddress env var; varRetentionDays ← flowlibs_ArchiveRetentionDays env var (default 90); varDeletedCount ← initialized to 0 as an integer counter.
Get Archived Records
SQL Server — GetItems_V2Queries the configured SQL table with an OData $filter that calculates the cutoff date dynamically (ArchivedDate le '{utcNow minus retentionDays}'). Returns all rows older than the retention threshold.
Loop Through Archived Records
Apply to eachFor each row returned by Get Archived Records: (1) SQL Server DeleteItem_V2 removes the row by its Id primary key, then (2) an Increment variable action bumps varDeletedCount by 1.
Send Cleanup Summary Email
Office 365 Outlook — SendEmailV2Sends a styled HTML email to the notification recipient summarizing the run: date/time, server, database, table, retention threshold, and total records deleted.
Environment Variables
| Schema name | Type | Default | Description |
|---|---|---|---|
| flowlibs_SqlServerName | String | your-server.database.windows.net | Target SQL Server hostname. |
| flowlibs_SqlDatabaseName | String | FlowLibsDemoDB | Target database. |
| flowlibs_SqlTableName | String | [dbo].[FormSubmissions] | Table containing archived records. |
| flowlibs_NotificationEmailAddress | String | alerts@yourcompany.com | Recipient address for the cleanup summary email. |
| flowlibs_ArchiveRetentionDays | String | 90 | Number of days after archival before a record is eligible for deletion. |
Connectors & Connections
| Connector | API name | Actions used |
|---|---|---|
| SQL Server | shared_sql | GetItems_V2 (query archived rows) DeleteItem_V2 (delete row by Id) |
| Office 365 Outlook | shared_office365 | SendEmailV2 (cleanup summary notification) |
Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.
Key Expressions
The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.
EXPR.01Cutoff date calculation
Builds the OData $filter string for GetItems_V2 by subtracting the retention-day count from utcNow().
EXPR.02Row ID reference in loop
Passed to DeleteItem_V2 as the primary-key value for the current iteration.
EXPR.03Email subject with deleted count
Composes the summary email subject with the final value of varDeletedCount.
Comments
Sign in to join the conversation.
Sign inNo comments yet. Be the first to share your experience with this flow.