We need to import bulk data (tasks) from an external source (Excel file from another system) into Dataverse.

 

 Scenario

We need to import bulk data (tasks) from an external source (Excel file from another system) into Dataverse.

🔹 Architecture Flow (Step by Step)

  1. Excel File Source

    • External system exports tasks into an Excel file.

    • File includes: title, description, category, duration, status.

    • File uploaded to Azure Blob Storage.

  2. Staging Database (SQL Server)

    • Before directly pushing into Dataverse, the file data is copied into a staging table in SQL Server.

    • Benefits:

      • Data validation before import.

      • Status tracking (import_status column: e.g., Pending, Success, Failed).

      • Easier error handling/re-runs.

  3. Azure Data Factory (ADF) Pipeline

    • Orchestrates the entire process.

    • Activity 1 (Copy Activity): Reads Excel from Blob → Loads into SQL staging table.

    • Activity 2 (Web Activity): Calls a Logic App to start processing staged rows.

  4. Logic App (Transformation + Import into Dataverse)

    • Triggered via HTTP by ADF.

    • Iterates through SQL staging rows.

    • For each row:

      • Calls Dataverse Connector → Creates a Task in Dataverse.

      • Updates the SQL row’s import_status to Success or Failed depending on outcome.

    • Uses Run After settings to handle retries and exceptions (so failed records can be marked and reprocessed later).

  5. Monitoring & Error Handling

    • Pipeline execution monitored in ADF.

    • Each row’s result tracked in SQL staging (import_status).

    • If Dataverse throttling or temporary error occurs, Logic App can retry only failed rows.

  6. Result

    • Tasks created in Dataverse.

    • Visible inside Model-driven Apps.

🔹 Key Benefits of This Approach

  • ✅ API Throttling Protection → Staging + batch import reduces hitting Dataverse limits.

  • ✅ Error Handling → Failed rows tracked in SQL; can be retried without re-importing everything.

  • ✅ Scalability → ADF pipeline manages orchestration, scaling to millions of rows.

  • ✅ Separation of Concerns → ADF handles orchestration, SQL handles staging & logging, Logic App handles Dataverse integration.

  • ✅ Flexibility → Instead of importing all data into Dataverse, you can also expose external data via Virtual Tables or custom UI controls, avoiding data duplication.

🔹 Alternative Consideration

At the end, they mention:
👉 Instead of importing everything, sometimes it’s better to expose data in real-time from external systems into Dataverse UI (via Virtual Tables, custom controls, or Power Pages).
This avoids:

  • Large storage consumption in Dataverse.

  • API throttling issues.

  • Data duplication.

Comments

Post a Comment

Popular posts from this blog

🔍 Dataverse + Azure Integration: Choosing Between Synapse Link and Microsoft Fabric

  Managing large-scale data in Microsoft Dataverse ? You're likely choosing between Azure Synapse Link and the newer Microsoft Fabric Link . Both unlock powerful analytics and reporting options—but with different trade-offs in performance, setup, and long-term cost. Let’s break it down 👇 📊 Power BI and Dataverse: Great, But With Limits Power BI uses TDS (Tabular Data Stream) to connect directly to Dataverse tables. Real-time querying works well for smaller datasets (tens of thousands of records). However, complex queries and relationships may cause timeouts after 5 minutes , making it unreliable for enterprise-scale analytics. 🔗 Azure Synapse Link: Power + Complexity ✅ Strengths: Designed for large datasets , converting Dataverse tables to CSV format for efficient querying and reporting. Highly customizable with Synapse Studio , Azure Data Lake , and Data Factory for deeper ETL/ELT capabilities. ⚠️ Watchouts: Requires technical setup : Azur...
Read more

⚡ Example: Rate Limiting in Azure API Management

  Scenario: You have a public API and want to limit each user to 5 calls per minute to prevent abuse or accidental overload. 🛡️ What is Rate Limiting? Rate limiting controls how many requests a client can make to your API within a specific time window . It helps: Prevent API abuse Protect backend systems Fairly distribute API usage 💡 Using the rate-limit-by-key Policy Azure APIM uses a policy called rate-limit-by-key . It limits requests based on a unique identifier — usually the caller's subscription key or IP address. 🔧 Example Policy (XML) Here’s how you can add this to your inbound policy : < inbound > < base /> < rate-limit-by-key calls = "5" renewal-period = "60" counter-key = "@(context.Subscription?.Key)" /> </ inbound > Explanation: Attribute Meaning calls="5" Maximum 5 calls allowed renewal-period="60" Time window of 60 seconds (1 minute...
Read more

In-Process vs Isolated Process Azure Functions: What’s the Difference?

 When building Azure Functions using .NET, one of the most important architectural choices you’ll face is deciding between In-Process and Isolated Process (Out-of-Process) hosting models. This decision impacts everything from startup time to dependency control and middleware capabilities. So let’s break it down. What is In-Process (InProc) Hosting? In the In-Process model: Your function code runs inside the same process as the Azure Functions runtime. It uses the same AppDomain and host environment . Benefits: Better performance (lower cold start time). Full access to features in the Microsoft.Azure.WebJobs SDK (like bindings, triggers, and logging). Easier to use existing .NET libraries and tools. Limitations: Tightly coupled to the Azure Functions runtime version. Less flexibility in middleware and custom startup configuration. Potential for dependency conflicts if runtime and your code use different versions of libraries. What is ...
Read more