Azure data factory - datetime error in copy activity when loading data in SQL database

Today we will learn on how to deal with loading datetime fields in the database using Data Factory

Scenario: Suppose you have a datetime field in source that you are trying to load into database through data factory v2. you sometime gets an error that datetime is converted into datetime2 or datetime misses loading the datetime column into database. follow the below steps:

Steps:

• In the copy activity, change the Mapping of the source to string.

• Use stored procedure approach to load the data into SQL database
• In the Sink section of copy activity, specify the stored procedure name, table type, and table type parameter

• In the stored procedure, convert string into datetime
• Link to stored procedure approach: https://www.datastackpros.com/2020/03/azure-data-factory-upsert-using.html
• Run the pipeline

Alternate solution:

•  Try changing the Mapping of the source to Datetime
•  specify the date format "yyyy-MM-dd"

• Run the pipeline
• it will convert the date into the specified format