ADF管道在复制活动中添加顺序值 [英] ADF Pipeline Adding Sequential Value in Copy Activity

问题描述

很抱歉，是否已在其他地方提出并回答了此问题.如果是这样，请确实在回复评论中引用该URL.这就是这种情况，

Apologies if this has been asked and answered elsewhere. If it is, please do refer to the url in comments on replies. So here is the situation,

我正在发出一个API请求，作为响应，我得到 auth_token ，我将其用于授权复制活动"中以JSON格式检索数据并将其接收到Azure SQL数据库.我能够将我在JSON中收到的所有元素映射到Azure SQL数据库的列.但是，仍然需要填充两列( UploadId 和 RowId ).

I am making an API Request, in response I get auth_token which I use in the Copy Activity as Authorization to retrieve data in JSON format and Sink it to Azure SQL Database. I am able to Map all the elements I'm receiving in JSON to the columns of Azure SQL Database. However, there are two columns (UploadId and RowId) that still need to be populated.

• UploadId 是一个GUID，在整个批次的行中都是相同的(我已经设法解决了这个问题)
• RowId 将是一个序列，从1到该批次条目的末尾，然后对于下一个批次(具有新的GUID值)，它将重置为1.

• UploadId is a GUID which will be same for the whole batch of rows (this I've managed to solve)
• RowId will be a sequence starting from 1 to end of that batch entry, and then for next batch (with new GUID value) it resets back to 1.

数据库将如下所示，

| APILoadTime |      UploadId     |    RowId    |
|  2020-02-01 | 29AD7-12345-22EwQ |      1      |
|  2020-02-01 | 29AD7-12345-22EwQ |      2      |
|  2020-02-01 | 29AD7-12345-22EwQ |      3      |
|  2020-02-01 | 29AD7-12345-22EwQ |      4      |
|  2020-02-01 | 29AD7-12345-22EwQ |      5      |
--------------------------------------------------> End of Batch One / Start of Batch Two
|  2020-02-01 | 30AD7-12345-22MLK |      1      |
|  2020-02-01 | 30AD7-12345-22MLK |      2      |
|  2020-02-01 | 30AD7-12345-22MLK |      3      |
|  2020-02-01 | 30AD7-12345-22MLK |      4      |
|  2020-02-01 | 30AD7-12345-22MLK |      5      |
--------------------------------------------------> End of Batch Two and so on ... 

Azure Pipeline的复制活动"中是否有一种方法可以实现此 RowId 行为……或者即使在Azure SQL数据库中也可以实现.

Is there a way in Azure Pipeline's Copy Activity to achieve this RowId behavior ... Or even if it's possible within Azure SQL Database.

很抱歉的描述很抱歉，在此先感谢您的帮助！问候

Apologies for a long description, and Thank you in advance for any help! Regards

推荐答案

非常感谢@Leon Yue和@JeffRamos，我设法弄清楚了解决方案，因此将其发布在这里，供其他可能遇到相同情况的其他人使用，

Thanks a lot @Leon Yue and @JeffRamos, I've managed to figure out the solution, so posting it here for everyone else who might encounter the same situation,

我找到的解决方案是在Azure数据工厂中使用存储过程，我将其称为"Azure数据流活动".这是我用来创建RowId种子函数的代码，

The solution I found was to use a Stored Procedure within Azure Data Factory from where I call the Azure Data Flow Activity. This is the code I used for creating the RowId seed function,

CREATE PROCEDURE resetRowId
AS
BEGIN
    DBCC CHECKIDENT ('myDatabase', RESEED, 0)
END
GO

一旦有了这个存储过程，我所做的就是这样，

Once I have this Stored Procedure, all I did was something like this,

这是为您做的，我将其保留为0的原因，以便当有新数据输入时，它又从1开始...

This does it for you, the reason I kept it 0 so that when new data comes in, it starts from 1 again ...

希望这对其他人也有帮助...

Hope this helps others too ...

感谢所有以某种方式提供帮助的人

Thank you all who helped in someway

这篇关于ADF管道在复制活动中添加顺序值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！