locked
Lookup Activity re-formats date string for stored procedure RRS feed

  • Question

  • Hello,

    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:

    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

    in Azure Data Factory:

    {
        "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"
                    }
                }
            ]
        }
    }

    and the results were:

    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"
    }  

    Does anyone know how to force ADF to send the full datetime or string to the stored procedure?

    Many thanks,

    Paul.

    Thursday, February 28, 2019 10:38 AM

All replies

  • 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.

    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

    Monday, March 18, 2019 6:06 PM