Facing issue with Copy activity to convert string to date using custm mapping

Question

Facing issue with Copy activity to convert string to date using custm mapping

AKYA 6

Trying to convert string "20220611'(source value) to date "2022-06-11"(Sink datetype is date ) using custom mapping.

Source is SAP and sink( mysql).

{
"sink": {
"name": "LSDate",
"type": "Date",
"format": "YYYY-MM-DD"
},
"source": {
"name": "LSDate",
"type": "string"
}
}

but still am unable to convert from string 'YYYYDDMM' to Date 'YYYY-DD-MM' using custom mapping Copy activity.
I need to pass column name as dynamic. because have multiple tables and columns.

HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2022-06-07T23:31:48.477+00:00
Hello @AKYA ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is that you want to convert to date whch is stored as string , please do let us know if its not accurate.
I do have SAY , but then i doubt that you can do that in the copt activity . As the name suggest copy is mostly used for moving data and has very less transformation function . I think you can do this .

Copy the data as string to MYSQL is a staging table and then move to the actual tablke , while doing to that you can perform the transformation .

Mapping Dataflow has very good transformation feantures but the challenge is that it does support SAP as source . If you can copy the data in a cloud storage ( using copy activity ) then we can use dataflow .
Please do let me if you have any queries.
Thanks
Himanshu
HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2022-06-08T16:19:06.593+00:00
Hello @AKYA ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is that you want to convert to date whch is stored as string , please do let us know if its not accurate.
I do have to say , but then i doubt that you can do that in the copy activity . As the name suggest copy is mostly used for moving data and has very less transformation function . I think you can do this .

Copy the data as string to MYSQL is a staging table and then move to the actual tablke , while doing to that you can perform the transformation .

Mapping Dataflow has very good transformation feantures but the challenge is that it does not support SAP as source . If you can copy the data in a cloud storage ( using copy activity ) then we can use dataflow .
Please do let me if you have any queries.
Thanks
Himanshu
AKYA 6 Reputation points

2022-06-08T16:50:18.22+00:00

Hi @HimanshuSinha-msft

Thanks for the response.

able to convert the string(YYYYMMDD) to date (YYYY-MM-DD) using copy activity with below code. but now facing issue with the source value(00000000) while converting into date. How can we convert "00000000" to null ?
"mapping": [
{
"sink": {
"name": "LSDate",
"type": "Date"

},
"source": {
"name": "LSDate",
"type": "Datetime",
"format": "YYYY-MM-DD"
}
}],
"typeConversion": true,
"typeConversionSettings": {
"allowDataTruncation": true,
"treatBooleanAsNumber": false,
"dateTimeFormat": "yyyyMMdd"
}
HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2022-06-08T19:04:53.427+00:00

Hello @AKYA ,,
How are you reading the records from SAP ? Are you reading froim a SAP table or SAP HANA ? SAP HANA does support and may be you can use query option to filter out the records with 0000 values .
I dont think SAP table support query .
https://learn.microsoft.com/en-us/azure/data-factory/connector-sap-hana?tabs=data-factory#parallel-copy-from-sap-hana
My apoloziges I am noy SAP literate .
Thanks
Himanshu

1 answer

Your answer

HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2022-06-07T23:31:48.477+00:00

Hello @AKYA ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is that you want to convert to date whch is stored as string , please do let us know if its not accurate.
I do have SAY , but then i doubt that you can do that in the copt activity . As the name suggest copy is mostly used for moving data and has very less transformation function . I think you can do this .

Copy the data as string to MYSQL is a staging table and then move to the actual tablke , while doing to that you can perform the transformation .

Mapping Dataflow has very good transformation feantures but the challenge is that it does support SAP as source . If you can copy the data in a cloud storage ( using copy activity ) then we can use dataflow .
Please do let me if you have any queries.
Thanks
Himanshu
HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2022-06-08T16:19:06.593+00:00

Hello @AKYA ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is that you want to convert to date whch is stored as string , please do let us know if its not accurate.
I do have to say , but then i doubt that you can do that in the copy activity . As the name suggest copy is mostly used for moving data and has very less transformation function . I think you can do this .

Copy the data as string to MYSQL is a staging table and then move to the actual tablke , while doing to that you can perform the transformation .

Mapping Dataflow has very good transformation feantures but the challenge is that it does not support SAP as source . If you can copy the data in a cloud storage ( using copy activity ) then we can use dataflow .
Please do let me if you have any queries.
Thanks
Himanshu
AKYA 6 Reputation points

2022-06-08T16:50:18.22+00:00

Hi @HimanshuSinha-msft

Thanks for the response.

able to convert the string(YYYYMMDD) to date (YYYY-MM-DD) using copy activity with below code. but now facing issue with the source value(00000000) while converting into date. How can we convert "00000000" to null ?
"mapping": [
{
"sink": {
"name": "LSDate",
"type": "Date"

},
"source": {
"name": "LSDate",
"type": "Datetime",
"format": "YYYY-MM-DD"
}
}],
"typeConversion": true,
"typeConversionSettings": {
"allowDataTruncation": true,
"treatBooleanAsNumber": false,
"dateTimeFormat": "yyyyMMdd"
}
HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2022-06-08T19:04:53.427+00:00

Hello @AKYA ,,
How are you reading the records from SAP ? Are you reading froim a SAP table or SAP HANA ? SAP HANA does support and may be you can use query option to filter out the records with 0000 values .
I dont think SAP table support query .
https://learn.microsoft.com/en-us/azure/data-factory/connector-sap-hana?tabs=data-factory#parallel-copy-from-sap-hana
My apoloziges I am noy SAP literate .
Thanks
Himanshu

Answer 1

Cheng, Vikki 10

I wonder if this is solved or not? As I am having the same issue when using copy activity type conversion setting (Screenshot below). I was able to convert a valid date, but now facing issue when the source value is 00000000, which is not a valid date. How to convert/or allow the date column to contain null value? Any thoughts?

User's image

Liang Ping Liam 0 Reputation points

2024-05-16T06:02:31.5666667+00:00

Hi Vikki,

I'm facing same issue as you. Did you manage to find a solution to this without building a dataflow?
Thanks!
Cheng, Vikki 10 Reputation points

2024-05-16T13:10:45.5+00:00

@Liang Ping Liam

We end up created a complicated pipeline, using manually created meta data.

Share via

Facing issue with Copy activity to convert string to date using custm mapping

1 answer

Your answer