Data type conversion in Azure Data Factory - csv

Question

I'm trying to push data to Salesforce through the Salesforce Connector. I am at the point where the data has been cleaned and transformed in several Data Flows and I am using a copy activity to sink the data to Salesforce. Originally I was using parquet files, But after several issues on the sink side I switched to CSV files. This resolved some errors I had but no I am dealing with formatting issues. I am confused as to why the data types are remembered inside data flows, but in the copy activity it lists all the fields as strings. This is causing me to not be able to send any dates or decimals to salesforce. How should I go about resolving this?

Answer 1

Why don't you use Dataflows for data type transformation ?

Inside Data Flows, before your Copy Activity, explicitly cast your fields to the required data types using derived columns or data type transformation features. Although the CSV source treats everything as a string, within a Data Flow, you can cast these strings to the appropriate data types needed by Salesforce.

In this case you can preview the data to check if the transformations are correct in Data Flow and debugging it to confirm the output types match Salesforce expected types.

Answer 2

Hello @Adrian Searles

Salesforce has its own data types and when you copy data from Azure Data Factory to Salesforce, the data types need to be mapped correctly.

In your case, it seems like the data types are not being mapped correctly and all the fields are being treated as strings. This is causing issues with sending dates and decimals to Salesforce.

To resolve this issue, you can try specifying the correct data types in the schema of the dataset. You can do this by defining the schema of the dataset in the Azure Data Factory pipeline. Here is an example of how you can define the schema of the dataset in the pipeline:

{
    "name": "SalesforceDataset",
    "properties": {
        "type": "SalesforceObject",
        "typeProperties": {
            "objectApiName": "MyTable__c"
        },
        "schema": [
            {
                "name": "Col_Date__c",
                "type": "DateTime"
            },
            {
                "name": "Col_Currency__c",
                "type": "Decimal"
            },
            {
                "name": "Col_Email__c",
                "type": "String"
            }
        ],
        "linkedServiceName": {
            "type": "LinkedServiceReference"
        }
    }
}

In the above example, the schema of the dataset is defined with the correct data types for each field. You can modify this example to match the schema of your dataset.

Once you have defined the schema of the dataset, you can use this dataset in the copy activity to copy data from Azure Data Factory to Salesforce.

I hope this helps! Let me know if you have any further questions.

---

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.