Looking for guidance on integrating third-party data into our domain

Question

Hi,
My company's transactional system is MS Dynamics 365. We need to import various third-party data into our domain. I'm a Power BI developer but have been assigned this project. I'm looking for guidance on a suitable database and integration tool.

Due to MS Dynamics 365 being a Dataverse, and Power BI's Power Query being the Power Platform Dataflows language, I was intending to use those two technologies to achieve my aim as both are familiar skillsets within my company. However, as I get further into the details of what the Dataverse can and cannot do, I get drawn into limitations and terms I do not understand and neither does anyone else in the company. Therefore, I've decided to not use the Dataverse, although I am still open to using Dataflows if that's possible.

What I need to do?

1. I need to ingest a dozen (and this number will grow over time) third-party data sources. Let's go on the premise this third-party data is kept in SQL databases. Each third-party data will be a small dataset - probably 20 columns and 20,000 rows. I simply need to get these small datasets saved into a database within our domain. This import will need to ability to be manually performed or scheduled.
2. Once the raw datasets are in our domain, I will need to join two or more datasets into one. I could join them and save into a physical table, or simply create a View upon the two or more datasets. This newly created joined table may then go on to be joined to another dataset within the same database either as a physical table or a View, and this could go on and on.
3. I don't foresee any complicated transformations during any ETL process. We just need to get the data imported.
4. Once any additional joins are complete and the datasets formed (whether as a physical table or as a View), the resulting datasets will be used to feed data to downstream processes. At the moment it's foreseen these downstream processes will be both Dynamics and Power BI consuming the datasets. I know Power BI can consume SQL Views but I'm not sure if the Dataverse has any limitations.

I'm currently learning towards recommending Azure SQL Server database as the storage technology.
It's the integration tool I am struggling to decide on. I could write SQL scripts to extract data from the third-parties and insert into the SQL Server db. I could write another SQL script to extract from various tables within this SQL Server db, join them, and then load to another table or use a SQL View to act as the same.

As you can see our requirements are basic. The recommendation would need to be a MS cloud-based technology. We would like for the recommendation to be a cheap(er) approach but with our basic needs, I wouldn't have thought this a problem.

If you guys can make suggestions and why for them, then I shall seek to understand them better.

Thanks.

Answer 1

You're already leaning towards using Azure SQL Database, and this choice aligns well with your needs. It offers easy scalability, robust security features, and native integration with other Microsoft services like Dynamics 365 and Power BI. You can store and manage the datasets efficiently while facilitating straightforward data manipulation tasks such as joins and views.

For the data integration part, considering that you prefer for Microsoft technologies and the simplicity of your ETL requirements, two primary tools come to my mind ADF and SSIS.

Why ADF ?

• Scheduling and Automation: ADF can automate the ingestion of data on a schedule, handling increasing volumes and varieties of data seamlessly.
• It offers built-in connectors for various data sources, including SQL databases, which matches your scenario of ingesting data from SQL databases.
• It can directly move data into Azure SQL Database and supports the execution of SQL scripts for data transformation or loading tasks.
• LADF provides a visual interface for designing ETL processes, which could be beneficial if you or others on your team are not deeply familiar with coding.

Why SSIS ?

If your environment is heavily reliant on SQL and you have expertise in managing SQL Server, SSIS could also be a good option.

• SSIS is highly customizable and powerful for complex data transformation needs.
• If your team is comfortable with SQL, SSIS scripts and tasks will be easy to manage.
• SSIS packages can be deployed to Azure and run within either Azure-SSIS integrated runtime in Data Factory or directly on an Azure VM.

Things to not forget aslo !

• Azure Data Factory and SSIS can be cost-effective, particularly when used within their scale and operational efficiencies. ADF, being fully managed, can reduce overhead costs associated with managing infrastructure.
• While ADF provides a more modern, low-code approach which might be easier to use and manage, SSIS offers deeper control and might require more technical proficiency.

Answer 2

Hi @Anon101，

You may consider using SSIS which is a platform for building enterprise-level data integration and data transformations solutions. Use Integration Services to solve complex business problems.

As you said you want to use Azure environment, you may try with Azure Data Factory（ADF) or Azure-SSIS IR.

To get the suitable recommendation and billing, you may contact Microsoft Support team for professional advice.

https://support.microsoft.com/en-us?icid=TopNavSupport

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.