查找活动重新格式化存储过程的日期字符串 [英] Lookup Activity re-formats date string for stored procedure
问题描述
您好，
似乎ADF v2中的查找活动无法在将参数作为参数发送到Azure SQL DW中的存储过程时重新格式化日期，即使参数指定为字符串。存储过程活动不会发生这种情况。我带了
来演示以下行为：
it seems that the lookup activity in ADF v2, can't help but reformat dates when sending as a parameter to a stored procedure in Azure SQL DW, even if the parameter is specified as a string. This does not happen with the stored procedure activity. I carried out the following to demonstrate the behaviour:
在Azure SQL DW中：
In Azure SQL DW:
CREATE TABLE [dbo].[ADFLookupTestTable]
(
[Pipeline name] [nvarchar](255) NULL,
[Pipeline trigger time] [datetimeoffset](7),
[Trigger time text] [nvarchar](50),
[Creation time] [datetimeoffset](7)
)
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX
)
GO
CREATE PROC [dbo].[ADFLookupTestProc] @PipelineName nvarchar(255), @PipelineTriggerTime datetimeoffset(7), @TriggerTimeText nvarchar(50)
AS
BEGIN
INSERT INTO [dbo].[ADFLookupTestTable] ([Pipeline name],[Pipeline trigger time],[Trigger time text],[Creation time])
SELECT @PipelineName, @PipelineTriggerTime, @TriggerTimeText, SYSDATETIMEOFFSET()
SELECT [Pipeline name],[Pipeline trigger time],[Trigger time text],[Creation time]
FROM [dbo].[ADFLookupTestTable]
END
GO：
{
"name": "ADFLookupTest",
"properties": {
"activities": [
{
"name": "Lookup1",
"type": "Lookup",
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"typeProperties": {
"source": {
"type": "SqlDWSource",
"sqlReaderStoredProcedureName": "[dbo].[ADFLookupTestProc]",
"storedProcedureParameters": {
"PipelineName": {
"type": "String",
"value": {
"value": "@pipeline().Pipeline",
"type": "Expression"
}
},
"PipelineTriggerTime": {
"type": "DateTimeOffset",
"value": {
"value": "@pipeline().TriggerTime",
"type": "Expression"
}
},
"TriggerTimeText": {
"type": "String",
"value": {
"value": "@formatDateTime(pipeline().TriggerTime,'yyyy-MM-ddTHH:mm:ss.FFFFFFFK')",
"type": "Expression"
}
}
}
},
"dataset": {
"referenceName": "myDwh_WholeWarehouse",
"type": "DatasetReference"
}
}
},
{
"name": "Stored Procedure1",
"type": "SqlServerStoredProcedure",
"dependsOn": [
{
"activity": "Lookup1",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"typeProperties": {
"storedProcedureName": "[dbo].[ADFLookupTestProc]",
"storedProcedureParameters": {
"PipelineName": {
"value": {
"value": "@pipeline().Pipeline",
"type": "Expression"
},
"type": "String"
},
"PipelineTriggerTime": {
"value": {
"value": "@pipeline().TriggerTime",
"type": "Expression"
},
"type": "DateTimeOffset"
},
"TriggerTimeText": {
"value": {
"value": "@formatDateTime(pipeline().TriggerTime,'yyyy-MM-ddTHH:mm:ss.FFFFFFFK')",
"type": "Expression"
},
"type": "String"
}
}
},
"linkedServiceName": {
"referenceName": "SQL_DataWarehouse_LinkedService",
"type": "LinkedServiceReference"
}
}
]
}
}，结果是：
SELECT *
FROM dbo.ADFLookupTestTable
Pipeline name Pipeline trigger time Trigger time text Creation time
------------- ---------------------------------- ---------------------------- ----------------------------------
ADFLookupTest 2019-02-28 09:56:45.0000000 +00:00 2/28/2019 9:56:45 AM 2019-02-28 09:56:48.2175002 +00:00
ADFLookupTest 2019-02-28 09:56:45.6789245 +00:00 2019-02-28T09:56:45.6789245Z 2019-02-28 09:57:06.2029931 +00:00即使查找活动的输入显示为：
even though the inputs to the lookup activity are showing as:
"PipelineTriggerTime": {
"type": "DateTimeOffset",
"value": "2019-02-28T09:56:45.6789245Z"
},
"TriggerTimeText": {
"type": "String",
"value": "2019-02-28T09:56:45.6789245Z"
} 有谁知道如何强制ADF将完整日期时间或字符串发送到存储过程？
Does anyone know how to force ADF to send the full datetime or string to the stored procedure?
非常感谢，
保罗。
推荐答案
我已经复制了这个问题。 感谢您提请我们注意。 我还没有找到改变Lookup活动行为的方法，但我发现了一些具有洞察力的东西。
I have reproduced the issue. Thank you for bringing this to our attention. I have not found a way to change the Lookup activity's behavior, but I have found something insightful.
你发现的行为仍在继续，即使我首先将时间格式与两个活动分开将其设置在字符串变量中并引用两个活动中的变量。 当我将字符串变量设置为所需时间格式的
形式的字符串文字时，这两个活动匹配。 这告诉我幕后有一些优化。
The behavior you found continues, even when I separate the time formatting from the two activities by first setting it in a string variable and referencing the variable in the two activities. When I set the string variable to a string literal in the form of the desired time format, the two activities match. This tells me there is some optimization going on behind the scenes.
如果你想看到这个改变，请在微软的反馈论坛Uservoice上投票。
If you would like to see this changed, please vote for it on Uservoice, Microsoft's feedback forum.
https://feedback.azure.com/forums/270578-data-factory
https://feedback.azure.com/forums/270578-data-factory
这篇关于查找活动重新格式化存储过程的日期字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
