Azure数据工厂活动副本:使用@pipeline().TriggerTime评估接收器表中的列 [英] Azure Data Factory activity copy: Evaluate column in sink table with @pipeline().TriggerTime

问题描述

使用Data Factory V2，我试图实现从一个Azure SQL数据库到另一个Azure SQL数据库的数据复制流.

With Data Factory V2 I'm trying to implement a stream of data copy from one Azure SQL database to another.

我已经将源表的所有列与接收器表进行了映射，但是在接收器表中我有一个空列，我想在其中输入管道运行时间.

I have mapped all the columns of the source table with the sink table but in the sink table I have an empty column where I would like to enter the pipeline run time.

有没有人知道如何在接收器表中填充此列而不在源表中显示它?

Does anyone know how to fill this column in the sink table without it being present in the source table?

下面是我的复制管道的代码

Below there is the code of my copy pipeline

{
"name": "FLD_Item_base",
"properties": {
    "activities": [
        {
            "name": "Copy_Team",
            "description": "copytable",
            "type": "Copy",
            "policy": {
                "timeout": "7.00:00:00",
                "retry": 0,
                "retryIntervalInSeconds": 30,
                "secureOutput": false,
                "secureInput": false
            },
            "typeProperties": {
                "source": {
                    "type": "SqlSource"
                },
                "sink": {
                    "type": "SqlSink",
                    "writeBatchSize": 10000,
                    "preCopyScript": "TRUNCATE TABLE Team_new"
                },
                "enableStaging": false,
                "dataIntegrationUnits": 0,
                "translator": {
                    "type": "TabularTranslator",
                    "columnMappings": {
                        "Code": "Code",
                        "Name": "Name"
                    }
                }
            },
            "inputs": [
                {
                    "referenceName": "Team",
                    "type": "DatasetReference"
                }
            ],
            "outputs": [
                {
                    "referenceName": "Team_new",
                    "type": "DatasetReference"
                  }
              ]
          }
      ]
  }
}

在接收器表中，我已经有列data_load，我想在其中插入管道执行日期，但是我目前没有映射它.

In my sink table I already have the column data_loadwhere I would like to insert the pipeline execution date, but I did not currently map it.

推荐答案

根据您的情况，请在SQL Server接收器中配置SQL Server存储过程，作为一种解决方法.

Based on your situation, please configure SQL Server stored procedure in your SQL Server sink as a workaround.

请按照此文档:

第1步:配置接收器数据集:

Step 1: Configure your Sink dataset:

步骤2:在复制活动中配置接收器部分，如下所示:

Step 2: Configure Sink section in copy activity as follows:

第3步:在数据库中，定义与sqlWriterTableType相同名称的表类型.请注意，表类型的架构应与输入数据返回的架构相同.

Step 3: In your database, define the table type with the same name as sqlWriterTableType. Notice that the schema of the table type should be same as the schema returned by your input data.

    CREATE TYPE [dbo].[testType] AS TABLE(
    [ID] [varchar](256) NOT NULL,
    [EXECUTE_TIME] [datetime] NOT NULL
)
GO

步骤4:在您的数据库中，定义与SqlWriterStoredProcedureName同名的存储过程.它处理来自指定源的输入数据，并合并到输出表中.请注意，存储过程的参数名称应与数据集中定义的"tableName"相同.

Step 4: In your database, define the stored procedure with the same name as SqlWriterStoredProcedureName. It handles input data from your specified source, and merge into the output table. Notice that the parameter name of the stored procedure should be the same as the "tableName" defined in dataset.

Create PROCEDURE convertCsv @ctest [dbo].[testType] READONLY
AS
BEGIN
  MERGE [dbo].[adf] AS target
  USING @ctest AS source
  ON (1=1)
  WHEN NOT MATCHED THEN
      INSERT (id,executeTime)
      VALUES (source.ID,GETDATE());
END

这篇关于Azure数据工厂活动副本:使用@pipeline().TriggerTime评估接收器表中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！