SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,494 questions
Azure Data Factory copy data activity is repeating values in Sink
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 83%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDF%3C/text%3E%3C/svg%3E)
Dawn Fenstermacher
0
Reputation points
I have a stored procedure in SQL 2016 that joins several tables, runs some basic calculations, and has a case statement. There are 8 columns total in my output.
I have created a simple Copy Data pipeline in ADF where I execute the stored procedure and copy the output to DB2.
All of my data types match.
In the SINK data, the columns that are a result of either a calculation or the case statement repeat their values.
For example, there is an invoice_date column that is unique for each row, but the net_price column, which is a calculated field from the stored procedure is repeated throughout the data set with the same value, per ID column.
For the next new column ID, the invoice_date column will be unique, but the calculated column of net_price will repeat itself with a value within this ID group.
The stored procedure does not have any grouping within itself.
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 8%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EST%3C/text%3E%3C/svg%3E)
Smaran Thoomu 22,505 Reputation points Microsoft External Staff2023-10-05T12:29:26.3933333+00:00 Hi @Dawn Fenstermacher Thank you for reaching out to the Azure community forum with your query. As I understand you are experiencing with the Azure Data Factory copy data activity where the columns that are a result of either a calculation or the case statement repeat their values in the sink data can be caused by several factors.
To resolve this issue, you can try the following steps:- Check the mapping of the source and sink schemas in the copy activity. Ensure that all columns in the source are properly mapped to the corresponding columns in the sink.
- Check the stored procedure to ensure that it is returning the correct data. You can execute the stored procedure in SQL Server Management Studio to verify the data returned by the stored procedure.
- If the stored procedure is returning the correct data, you can try modifying the copy data activity to use a staging table. In this approach, you can copy the data from the stored procedure to a staging table and then copy the data from the staging table to the sink data.
- If the issue persists, you can try using the Data Flow activity in Azure Data Factory. The Data Flow activity provides a visual interface for building data transformation pipelines. You can use the Data Flow activity to transform the data from the stored procedure before copying it to the sink data.
Reference: Data Flow activity in ADF
I hope this helps! Let me know if you have any further questions. -
Smaran Thoomu 22,505 Reputation points Microsoft External Staff
2023-10-06T13:12:55.5966667+00:00 @Dawn Fenstermacher We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
-
Dawn Fenstermacher 0 Reputation points
2023-10-09T13:23:25.3+00:00 It turns out that the issue is likely one with DB2 rather than ADF. I tried a test case whereby I removed DB2 as the sink and replaced it with an on-premise SQL database, and the values were written correctly.
I believe there my be nuances to writing to DB2 via ADF. I'm all ears to any suggestions for solutions to this issue!
-
Smaran Thoomu 22,505 Reputation points Microsoft External Staff
2023-10-09T16:31:13.73+00:00 @Dawn Fenstermacher Thank you for the update. If the issue is likely with DB2 rather than Azure Data Factory, then you may need to investigate the DB2 configuration and settings to determine the root cause of the issue.
Here are some steps you can take:- Verify that the DB2 connector is properly configured in ADF. Ensure that the connection string, authentication type, and other settings are correct and match the DB2 database configuration. You can refer to the DB2 connector in V2 documentation for more information.
- Check the DB2 database configuration and ensure that it is properly configured to receive data from ADF. For example, check the package collection property to ensure that the needed packages are created for the user.
- Verify that the DB2 database is accessible from the ADF environment. Ensure that the network connectivity is properly configured and that any firewalls or security groups are properly configured to allow traffic between ADF and DB2.
- Check the DB2 logs for any errors or warnings related to the data transfer. This can help you identify any issues with the DB2 database or the DB2 connector.
It is also important to note that the issue could be related to the data itself, such as duplicate values or skew in the data distribution. You may need to investigate the data further to identify any issues or anomalies.
Reference: Copy data from DB2 using Azure Data Factory
I hope this helps! Let me know if you have any further questions.
Sign in to comment
Sign in to answer