I'm building a system to upload my local CSV file which the data is updated daily into Dynamics 365 by scheduled process. But before that, I want to make a few adjustments (data cleansing) of my CSV file.
Do I have to setup SQL Server as a middleman between my local files and Dynamics? Or is there already data cleansing tools in Dynamics? What is the best way to perform this?
There is no OOB way to do the "Transform" piece of ETL inside Dynamics CRM. Normally we will use third party tools like Kingswaysoft with SSIS or Scribe to do that data massage.
As you are already building some "scheduled system" to do systematic data loading, You can parse/transform the data in your code itself before importing or some SQL staging tables based on the needed complexity.