Azure Data Factory SqlErrorNumber 8672: A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times

Scenario: We have a copy activity in the data factory pipeline that, copies data from Azure Blob Storage (ADLS) to Azure SQL. in the sink, we are using SQL stored procedure to upsert the data using Merge statement. In the stored procedure, we are passing the Table type that holds the data from source.

While running the pipeline, it fails giving the below error message:

• Error: The MERGE statement attempted to UPDATE or DELETE the same row more than once.
• { "errorCode": "2200", "message": "ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.',Source=,''Type=System.Data.SqlClient.SqlException,Message=The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.,Source=.Net SqlClient Data Provider,SqlErrorNumber=8672,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=8672,State=1,Message=The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.,},],'", "failureType": "UserError", "target": "Copy data to Azure SQL", "details": [] }

Solution: On Analyzing, we found that in our SQL table, we had one column set to not null that we are no longer using in our database. after deleting this column from the SQL table, our pipeline ran successfully. 

• If we require this column, then we would have checked if the source is sending the data in this column or not. if the data was not regular (we see some nulls), then we would have edited the schema of the table to make this column allow Nulls.

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

Azure Devops - Connect Data Factory to Azure DevOps Git repository

Today we will learn on how to set the code repository in Data Factory and link it with Azure Git

There are two ways to set up code repository in Data Factory

1. Git
2. Azure Git

Today, we will be using Azure Git as a code repository in Data Factory.

To Link Azure Git to Data Factory, you first have to setup Azure DevOps:

1. Go to Azure Devops (https://aex.dev.azure.com/me?mkt=en-US)
2. Create an Organization (if not created already)
3. Add a project in that organization
4. Assign team members (if any) and give them the appropriate permissions:
1. For team members to create Git repos, they should have project admin rights
2. If you want them to only contribute to existing project/Git Repo but not have them as an admin, assign them the contributor rights.
3. Now, go inside the project and create a Test git repo

Once you have created an organization, project and a repo inside the project, you are ready to link the Azure code repository to Data Factory. 

Your Azure Devops structure should look like below:

Let's Link Code repository to Data Factory.

Go to your data factory and click "Author and Monitor" in the Overview Section. 

• It will open a new link on the browser and take you inside the data factory instance.
• In the data factory Overview section, click "Setup code repository"
• Select the below options
• Azure Git as Code repository as repository type
• Your Azure Devops account (Organization)
• Your Project (created within organization)
• Your Git repo (created inside project)
• Collaboration Branch: you can choose master.
• Once you publish in data factory, it will create a new branch "Adf_Publish" where it will store it's ARM deployment templates.

• Click Apply.
• You are done.

Go inside the data factory by clicking "Author" and you will see your code repository linked.

Enjoy :)