Lookup activity and parameterization: Getting a new type of error but my code worked yesterday


  • Today (tuesday 18/9) I'm seeing new error behavior in some of my pipelines. These pipelines worked yesterday which I can see from scheduled runs. So I'm wondering if you have deployed changes to ADF that we haven't been made aware of?

    The error:

    { "errorCode": "2200", "message": "Failure happened on 'Source' side. ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Conversion failed when converting date and/or time from character string.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Conversion failed when converting date and/or time from character string.,Source=.Net SqlClient Data Provider,SqlErrorNumber=241,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=241,State=1,Message=Conversion failed when converting date and/or time from character string.,},],'", "failureType": "UserError", "target": "Copy Data1" }

    The error is due to a pipeline trying to use a script from a lookup activity that also works by having parameters injected to it from the pipeline. I fetch a sql script from github and use it in a copy activity by outputting the content of the lookup activity. In the sql script I use for instance '@{pipeline().parameters.Start_year}' to limit my query. My guess is that now ADF inserts quotes around the parameter output which screws up my string in the query. For instance, instead of '2012-01-01' it becomes '"2012-01-01"' and the sql database doesn't like converting that kind of string to a date. When I copy paste the exact string that gets returned from the lookup into the query field of the copy activity the pipeline works, which makes me believe even more that I'm correct on this. 

    This method has worked for me for quite a while but now it suddenly doesn't work anymore, and the changes must have been made since yesterday (17/9). Please help me look into what's causing this. I have several production pipelines that I now have to hard-code values into the scripts in order to make them work.


    Jonathan Åkerblom
    Data Scientist
    Forefront Consulting, Sweden

    • Edited by JAkerblom Tuesday, September 18, 2018 2:40 PM Better title
    Tuesday, September 18, 2018 2:33 PM

All replies