none
Error: ADF V2: Unable to Parse DateTime Format / Convert DateTime Format for StoredProc Parameter

    Question

  • ADF V2: When setting up Source for Copy Activity in ADF V2, for USE Query I have selected Stored Procedure, selected the stored procedure and imported the parameters.

    We have the following parameters

    AdfWindowEnd
    AdfWindowStart
    taskName
    for AdfWindowEnd and AdfWindowStart, type was recognized by default as DateTime and in the Value Section I tried all the below formats one after the other

    $$Text.Format('{0:yyyy-MM-dd HH:mm:ss}', windowEnd)
    $$Text.Format('{0:yyyy-MM-dd HH:mm:ss}', windowEndTime)
    Text.Format('{0:yyyy-MM-dd HH:mm:ss}', windowEndTime)
    Text.Format('{0:yyyy-MM-dd HH:mm:ss}', windowEnd)
    $$Text.Format('{0:yyyy-MM-ddTHH:mm:ss}', windowEndTime)
    windowEnd
    windowEndTime
    {0:yyyy-MM-dd HH:mm:ss}
    {0:yyyy-MM-ddTHH:mm:ss.FFF\Z}
    $$Text.Format('{0:yyyy-MM-dd}', windowEnd)
    $$Text.Format('{0:yyyymmddThhmmssffffff}', windowEndTime)
    $$Text.Format('{0:yyyy-MM-dd HH:mm:ss.FFF}', windowEnd)
    $$Text.Format('{0:yyyy-MM-dd HH:mm:ss.FFF}', windowEndTime)
    $$Text.Format('{0:yyyy-MM-ddTHH:mm:ssZ}', windowEnd)
    $$Text.Format('{0:yyyy-MM-ddTHH:mm:ssZ}', windowEndTime)
    $$Text.Format('{0:yyyy-MM-ddTHH:mm:ss.FFF\Z}', windowEnd)
    $$Text.Format('{0:yyyy-MM-ddTHH:mm:ss.FFF\Z}', windowEndTime)

    I am running into following error when I try to debug the activity with any of the above formats and not even single format is working.

    ERROR MESSAGE:

    "errorCode": "2200",
        "message": "ErrorCode=UserErrorInvalidParameterInStoredProcedure,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property 'Value' is invalid for the stored procedure parameter 'AdfWindowEnd'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.FormatException,Message=$$Text.Format('{0:yyyy-MM-dd HH:mm:ss}', windowEndTime) is not a valid value for DateTime.,Source=System,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'",

    Please help me understand the correct format here.


    Tuesday, October 9, 2018 8:46 AM

Answers

  • Hi Kishore,

    If I understand right, "windowsEnd" should be a pipeline parameter, right? If so, please pass:

    @formatDateTime(pipeline().parameters.windowEndTime,'0:yyyy-MM-ddTHH:mm:ssZ')

    to the stored procedure parameter. ADFv2 no longer supports format like: "$$Text.Format("....

    This link gives you more information.

    Thanks.

    Tuesday, October 9, 2018 9:02 AM

All replies

  • Hi Kishore,

    If I understand right, "windowsEnd" should be a pipeline parameter, right? If so, please pass:

    @formatDateTime(pipeline().parameters.windowEndTime,'0:yyyy-MM-ddTHH:mm:ssZ')

    to the stored procedure parameter. ADFv2 no longer supports format like: "$$Text.Format("....

    This link gives you more information.

    Thanks.

    Tuesday, October 9, 2018 9:02 AM
  • Thanks Wang for the reply.

    I created Parameter in the pipeline now and used it the below way and it worked.

    @pipeline().parameters.windowEndTime.

    I have another ask.

    I have a scenario like copying data to blob locations from a Table to Blob locations.

    While reading from Table(differentiated by Regions). I used a LookUp and ForEach activity to Push the data to blob location

    Table --> Regions/R1/File.Gz

    Table --> Regions/R2/File.Gz

    Now I have to read the respective Region file from blob and push to different Region specific databases.

    I am not able to embed another copy activity in the same pipeline because the destination database changes based on the Region.

    Is there a provision in ADF V2, to dynamically change the Linked Service or Connect to Linked Service at Run time?

    is there any way where single copy activity can connect multiple sink dataset one by one in same foreach or dynamically change the database connection in the sink of copy activity?



    Thursday, October 11, 2018 5:10 AM
  • Update:

    I am able to do on my 2nd ask about dynamic dataset in linked service through this article.

    https://docs.microsoft.com/en-us/azure/data-factory/parameterize-linked-services 

    Thanks.

    Thursday, October 11, 2018 8:17 AM