locked
For each loop to add SQL Data to files in Datalake gen2 error Failure happened on 'Source' side 'Incorrect syntax near '2014-12-01T00:00:00'.',Source RRS feed

  • Question

  • I have a Foreach loop that moves snapshot data from a database to a Datalake

    Lookup Dates is based on a List of distinct dates which is working. (The dates are in DAte2 Format because the source data is DAte2 Format

    It appears to be erroring in the copy section of the Foreach Loop. 

    This sees to be fine. Im using the Output.value from Lookup dates sequentially

    I believe its the query section. Here is the error message I get on every run

    { "errorCode": "2200", "message": "Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near '2014-12-01T00:00:00'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near '2014-12-01T00:00:00'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near '2014-12-01T00:00:00'.,},],'", "failureType": "UserError", "target": "CopyMemberDailyMetrics", "details": [] }

    It appears to be incorrect syntax near date but I don't know what to change the code too to make it work.

    Or could it be something else like the date

    Any help would be appreciated


    Debbie

    Tuesday, January 7, 2020 10:57 AM

Answers

All replies

  • Please try using below like query 

    SELECT Column1, Column2
    FROM dbo.WhateverDailyMetrics
    WHERE DateUTC = '@{item().DateUTC}'


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Tuesday, January 7, 2020 11:03 AM
  • I have tried that but Im getting the exact same error 

    { "errorCode": "2200", "message": "Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near '2016-03-11T00:00:00'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near '2016-03-11T00:00:00'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near '2016-03-11T00:00:00'.,},],'", "failureType": "UserError", "target": "CopyMemberDailyMetrics", "details": [] }

    Reading all the documentation I thought that Equals was EQ rather than =

    Very odd that Im getting the same message

    I tried a test which was to using the above script SELECT FROM without 

    WHERE DateUTC ='@{item().DateUTC}'

    In an attempt to see if its this bit causing the issue

    But I get the error

    { "errorCode": "2200", "message": "Failure happened on 'Sink' side. ErrorCode=AdlsGen2OperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ADLS Gen2 operation failed for: Operation returned an invalid status code 'BadRequest'. Account: 'reportingstorage'. FileSystem: 'daily-metrics'. Path: ''@concat('dailymetrics_',{@item().UTCDate},'.csv')'\n\n'. Message: 'Bad Request'..,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.Azure.Storage.Data.Models.ErrorSchemaException,Message=Operation returned an invalid status code 'BadRequest',Source=Microsoft.DataTransfer.ClientLibrary,'", "failureType": "UserError", "target": "CopyMemberDailyMetrics", "details": [] }

    Which is clratly another issue that comes after the previous. So its definitely

    WHERE DateUTC = '@{item().DateUTC}' causing the issue but again, I dont know what to do to resolve it


    Debbie



    Tuesday, January 7, 2020 11:50 AM
  • can you try concat function instead of putting string value directly like concat('DateUTC', @{item().DateUTC})


    zzzSharePoint

    Tuesday, January 7, 2020 12:24 PM
  • Im not quite sure what you mean about this.

    After the change in the Copy script I have

    SELECT Column1, Column2 FROM dbo.WhateverDailyMetrics WHERE DateUTC ='@{item().DateUTC}'

    Are you saying change that to just?

    concat('DateUTC', @{item().DateUTC})

    Im not really sure how this would work?


    Debbie

    Tuesday, January 7, 2020 1:39 PM

  • But I get the error

    { "errorCode": "2200", "message": "Failure happened on 'Sink' side. ErrorCode=AdlsGen2OperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ADLS Gen2 operation failed for: Operation returned an invalid status code 'BadRequest'. Account: 'reportingstorage'. FileSystem: 'daily-metrics'. Path: ''@concat('dailymetrics_',{@item().UTCDate},'.csv')'\n\n'. Message: 'Bad Request'..,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.Azure.Storage.Data.Models.ErrorSchemaException,Message=Operation returned an invalid status code 'BadRequest',Source=Microsoft.DataTransfer.ClientLibrary,'", "failureType": "UserError", "target": "CopyMemberDailyMetrics", "details": [] }

    Earlier the error was at source and I think you are getting error at Sink now. I assume you are trying to generate a file with name starts with "dailymetrics" appended by date. 

    I did a test and worked for me. 

    Below is my Input: 

    Output:

    Settings in pipeline:


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Tuesday, January 7, 2020 2:36 PM
  • I'm finding the video a little difficult to follow.

    When I get to Sink Data set I don't have Filename. Are you using datalake Gen2 as the data store? This is what Im sending my data to as the sink. 

    All I have is File Extension. And I set up the file name in the data set. The rest is the same as yours above 

    Ive changed the File name because its possible I dont need .csv in there. Tried again

    { "errorCode": "2200", "message": "Failure happened on 'Sink' side. ErrorCode=AdlsGen2OperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ADLS Gen2 operation failed for: Operation returned an invalid status code 'BadRequest'. Account: 'reportingstorage'. FileSystem: 'member-daily-metrics'. Path: ''@concat('memberdailymetrics_',{@item().UTCDate})'\n\n'. Message: 'Bad Request'..,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.Azure.Storage.Data.Models.ErrorSchemaException,Message=Operation returned an invalid status code 'BadRequest',Source=Microsoft.DataTransfer.ClientLibrary,'", "failureType": "UserError", "target": "CopyMemberDailyMetrics", "details": [] }

    Interesting. Even though I've tested with the date = Date is still showing me this error

    I have everything set up correctly with my app connected to the correct container (read Write and Execute)

    So for me its still not working


    Debbie


    Tuesday, January 7, 2020 4:01 PM
  • All I have is File Extension. And I set up the file name in the data set. The rest is the same as yours above 

    I have used Azure blob storage as Sink but the steps should be same for ADLS as well. In Sink dataset, I have parameterised the file name box. 

    Expression in Sink: @concat('DailyMetrics', string(item().DateUTC))

    You can have a look at this as well: 
    https://social.technet.microsoft.com/wiki/contents/articles/53406.azure-data-factory-dynamically-add-timestamp-in-copied-filename.aspx

    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Tuesday, January 7, 2020 5:19 PM
  • I don't know how I did it but I got through that error. And using your above info I may have resolved the concatenation. Ill add my blog post to this with more info. Thankyou


    Debbie


    Wednesday, January 8, 2020 11:55 AM