Date column in sink table is getting NULL values

Question

Hi,

I have two fields in my source .csv file which is Date1(format- YYYYMMDD) and Date2(format- YYYYMMDD), I have corresponding table in my SQL database with the same fields with data type as datetime2. Now I am trying to load the data from my source file to sink table using mapping DF. But the values in my table are getting populated with NULL values, is it due to the string to datetime2 conversion from source to sink causing issue?

Answer 1

Hello @Harshita Vishwakarma

It is possible that the string to datetime2 conversion is causing the issue.

When copying data from a source to a sink, it is important to ensure that the data types in the source and sink match. In your case, you are trying to copy data from a string data type in the source to a datetime2 data type in the sink.

To resolve this issue, you can use the Derived Column transformation in your mapping data flow to convert the string data type to a datetime2 data type.

Here's an example of how you can do this:

1. Add a Derived Column transformation to your mapping data flow.
2. In the Derived Column transformation, add a new column with a datetime2 data type.
3. Use the toDate() function to convert the string data type to a datetime2 data type. Here's an example of how you can use the toDate() function: toDate(Date1, 'yyyyMMdd') This will convert the Date1 string to a datetime2 data type using the 'yyyyMMdd' format.
4. Repeat the above steps for the Date2 field. Once you have added the Derived Column transformation and converted the data types, you can map the new datetime2 columns to the corresponding columns in your sink table. This should resolve the issue of NULL values being populated in your sink table.

---

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.

Answer 2

@Harshita Vishwakarma since you cannot accept your own answer, let me reformulate it ;

It appears that the YYYYMMDD date format isn't compatible with Azure Data Factory resulting in NULL values when using the toDate function.

To resolve this, the date format needs to be adjusted by inserting hyphens to make it compatible for loading into the destination system.

More information on this can be found in the ADF data flow expressions documentation: Microsoft Docs.

Answer 3

You may need to use a Derived Column where you explicitly convert the string date from the source to a datetime format that SQL can recognize :

toTimestamp(Date1, 'yyyyMMdd')
toTimestamp(Date2, 'yyyyMMdd')