I'm a little bit lost regarding the Azure data factory.
My goal is to retrieve data from our company postgresql database and transform it into an Azure SQL database (which can later be accessed by Power BI).
I created a pipeline and tried to use "Data Flow" as an activity. However, I cannot select my postgresql database as a source there. Valid sources are for example other Azure SQL databases.
So I tried to perform the "Copy Data" activity to copy data from the postgresql database into an Azure SQL database (1) and afterwards transform the data using a "Data Flow" into an Azure SQL database (2) (which has a different table structure).
Is that a good and valid approach?
There are a couple of problems I'm having with this approach:
- I cannot select multiple tables from my source postgresql dataset (neither from my target dataset
Azure SQL database (1)). Azure gives me the option to select a single table or "None". I could cope with this problem if I would create several datasets each with a different table but that seems wrong and not feasible.
- I am not sure what would happen if the data is already present in the
Azure SQL database (1). In the "Data Flow" activity I have the option to select "Allow insert", "Allow update" or different key columns. In the "Copy Data" activity I do not have this opportunity.
Could someone guide me into the right direction please?