Search code examples
azuretransformationazure-synapseazure-data-factory

What to do when my Data source is not supported by Azure Synapse's Data Flow?

I am trying to transform data from Salesforce before loading it to dedicated SQL pool. When I try to create a dataset from Synapse's Dataflow, I am not able to choose Salesforce a Data store: enter image description here

Can anyone suggest how to transform data from Salesforce or any other Datasource that is not supported by Dataflow?

Solution

  • As per the Official Documentation, Currently Dataflows does not support Salesforce data as source or sink.

    If you want, you can raise the feature request in the Synapse portal.

    enter image description here

    As an alternate, you can use Copy activity in the Azure Data factory to copy data from Salesforce to Dedicated SQL pool and then you can transform it using Dataflows in synapse from Dedicated SQL DB to Dedicated SQL DB.

    Follow the below steps to achieve your requirement:

    • First create a Data Factory Workspace.
    • Select the Author hub and a create a pipeline. Now, drag the copy activity from the workspace and select the source. You can see that Salesforce is supported when you select new source dataset. Select it and create a linked service for that.

    enter image description here

    • Now, select the sink dataset and click on Azure Synapse analytics.

    enter image description here

    • Create a linked service for the Dedicated SQL database and select it.

    enter image description here

    • Then, you can select the table in the Dedicated SQL and copy your data by running this.
    • After this copy, go to Synapse workspace and click on the Source of the Dataflow. Select the Azure Synapse Analytics in source and click on continue.

    enter image description here

    • Now, click on New to create linked service for the SQL DB. Give the subscription and server name and authenticate with your database.

    enter image description here

    • After the creation of linked service, select it and give your table which is result of the copy in the DB.

    enter image description here

    • Now, go to sink and select Azure Synapse Analytics and create another linked service for it as same above and select the resultant table in DB which you want after transform.

    enter image description here

    • By following the above process, we can achieve the transformation from Salesforce data to Dedicated SQL DB.

    Can anyone suggest how to transform data from Salesforce or any other Datasource that is not supported by Dataflow?

    You can try this approach for the data stores which are not supported by the Data flows and please refer this to check various data stores supported by Copy activity before doing this process for the other data stores.