Data type conversion in Azure Data Factory - csv

Adrian Searles 0

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?

Smaran Thoomu 9,610 Reputation points Microsoft Vendor

2024-03-29T10:09:55.5+00:00
Hi @Adrian Searles

Thanks for the question and using MS Q&A platform.

It sounds like you are experiencing issues with data type conversion in Azure Data Factory when using the Salesforce Connector to sink data from CSV files.

When using the copy activity to sink data to Salesforce, the data types of the fields are determined by the schema of the destination table in Salesforce. If the schema of the destination table in Salesforce is different from the schema of the source data, you may encounter issues with data type conversion. To resolve this issue, you can try the following steps:

Check the schema of the destination table in Salesforce: Make sure that the schema of the destination table in Salesforce matches the schema of the source data. If the schema of the destination table is different, you may need to modify the schema of the source data to match the schema of the destination table.

Use data type conversion in the copy activity: You can use the data type conversion feature in the copy activity to convert the data types of the fields in the source data to match the data types of the fields in the destination table. To do this, you can add a "Data flow" activity before the copy activity and use the "Derived Column" transformation to convert the data types of the fields.

Use a mapping data flow: Instead of using a copy activity, you can use a mapping data flow to sink data to Salesforce. Mapping data flows provide more flexibility in data type conversion and allow you to map the fields in the source data to the fields in the destination table.

I hope this helps! Let me know if you have any further questions.
Smaran Thoomu 9,610 Reputation points Microsoft Vendor

2024-04-01T07:40:17.9533333+00:00

@Adrian Searles Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Adrian Searles 0 Reputation points

2024-04-01T13:13:46.7066667+00:00

Hi @Smaran Thoomu , I'm still having issues. In the delimited text dataset the schema shows all strings. I cleared that. I have a data flow that sinks records with the required, matching data types into that delimited text file. After uploaded I try to import the schema on the dataset and they are all still strings. Unfortunately salesforce isnt a sink option in data flows.

Do you mean a data flow activity when you say mapping data flow?
Adrian Searles 0 Reputation points

2024-04-01T13:36:58.0033333+00:00
Smaran Thoomu 9,610 Reputation points Microsoft Vendor

2024-04-03T09:54:01.69+00:00

@Adrian Searles I apologize for the confusion in my previous response. Yes, I meant to say "Data Flow" activity instead of "Mapping" data flow.

Based on your updated query, it seems that you have cleared the schema in the delimited text dataset and have a data flow that sinks records with the required, matching data types into that delimited text file. However, when you try to import the schema on the dataset, they are still all strings.

1 answer

Amira Bedhiafi 15,216 Reputation points

2024-03-29T16:55:46.4833333+00:00

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.
Please sign in to rate this answer.
Adrian Searles 0 Reputation points

2024-04-01T13:09:53.1133333+00:00

Hi @Amira Bedhiafi , thanks for your answer. I am using data flows before to cast my fields to the required data type. In the data flow, before my sinking to the destination dataset, a delimited text file. In the flow the data types are correct, although in the copy activity everything is being treated as a string. And inside the data flow, salesforce isn't a sink option.

Amira Bedhiafi 15,216 Reputation points

2024-04-01T13:15:43.04+00:00

It can be something related to the CSV files then :)

Adrian Searles 0 Reputation points

2024-04-01T13:33:31.16+00:00

@Amira Bedhiafi Is that a feature of Azure Data Factory that delimited text datasets are always string types?

Amira Bedhiafi 15,216 Reputation points

2024-04-01T14:42:28.1466667+00:00

I don't understand you ?
Sign in to comment