Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Syndicate_Admin
Administrator
Administrator

Copy data from sharepoint list to azure SQL

Hi all,

 

I have a requirement where client wants to migrate all data from several SharePoint lists to azure SQL. We have set up the azure SQL database (there are no tables created yet). I found several articles on achieving it using dataflows, azure data factory and power automate. What would be the best to accomplish it? 

** NOTE: One SharePoint  list has around 3 lakhs records.

1 ACCEPTED SOLUTION
larsheinemann
Frequent Visitor

  1. Dataflows in Power BI:

    • Pros:
      • Integrated with Power BI, making it suitable if you are already using Power BI for your analytics.
      • Can transform data using Power Query, which provides a user-friendly interface for data transformation.
    • Cons:
      • More suited for analytical scenarios rather than straightforward data migration.
      • Less scalable compared to Azure Data Factory.
  2. Azure Data Factory (ADF):

    • Pros:
      • Highly scalable and designed for complex ETL (Extract, Transform, Load) processes.
      • Provides more granular control over the data integration process.
      • Supports fault tolerance, monitoring, and logging.
      • Can handle large volumes of data and complex transformations.
    • Cons:
      • Might be overkill for simple, one-time migrations.
      • Has a steeper learning curve.
  3. Power Automate:

    • Pros:
      • User-friendly interface with a low-code environment.
      • Good for real-time or near-real-time data integration scenarios.
      • Can trigger flows based on SharePoint list events.
    • Cons:
      • Not designed for large scale ETL processes.
      • May encounter limitations with data volume and execution frequency.

Recommendation:

  • If this is a one-time migration and you're looking for the simplest solution, Power Automate might be the easiest way to go, especially if you don't have any complex transformations.

  • If you anticipate regular data migrations in the future, or if the data volume is large, or if there's a need for complex transformations, Azure Data Factory would be a more robust choice. ADF is especially powerful for setting up repeatable, scalable, and complex ETL processes.

  • Dataflows would be a middle ground, especially if you're already using Power BI and need to transform the data for analytics purposes.

View solution in original post

1 REPLY 1
larsheinemann
Frequent Visitor

  1. Dataflows in Power BI:

    • Pros:
      • Integrated with Power BI, making it suitable if you are already using Power BI for your analytics.
      • Can transform data using Power Query, which provides a user-friendly interface for data transformation.
    • Cons:
      • More suited for analytical scenarios rather than straightforward data migration.
      • Less scalable compared to Azure Data Factory.
  2. Azure Data Factory (ADF):

    • Pros:
      • Highly scalable and designed for complex ETL (Extract, Transform, Load) processes.
      • Provides more granular control over the data integration process.
      • Supports fault tolerance, monitoring, and logging.
      • Can handle large volumes of data and complex transformations.
    • Cons:
      • Might be overkill for simple, one-time migrations.
      • Has a steeper learning curve.
  3. Power Automate:

    • Pros:
      • User-friendly interface with a low-code environment.
      • Good for real-time or near-real-time data integration scenarios.
      • Can trigger flows based on SharePoint list events.
    • Cons:
      • Not designed for large scale ETL processes.
      • May encounter limitations with data volume and execution frequency.

Recommendation:

  • If this is a one-time migration and you're looking for the simplest solution, Power Automate might be the easiest way to go, especially if you don't have any complex transformations.

  • If you anticipate regular data migrations in the future, or if the data volume is large, or if there's a need for complex transformations, Azure Data Factory would be a more robust choice. ADF is especially powerful for setting up repeatable, scalable, and complex ETL processes.

  • Dataflows would be a middle ground, especially if you're already using Power BI and need to transform the data for analytics purposes.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors