locked
Copy Source data with a Query where Date > a data in a lookup RRS feed

  • Question

  • I'm currently putting together a Pipeline that takes distinct dates from source to destination

    We have a source server containing the data (Which we cant change or add to) and our own destination server and database and a data lake

    The column in Question is DateUTC Datetime2 moving to column in Destination DateUTC Datetime2 

    1. LookupWatermarkOld gets the date from the watermark table (Currently I'm not quite sure how to use it)

    It contains the Query 

    SELECT MAX(WatermarkDate2) AS WatermarkValue From [audit].[IdWatermarks]
    WHERE WatermarkDate2 IS NOT NULL 
    AND TableName = 'ExampleTable'

    2. CopyDailyMetricsDateUTC

    Source currently contains this query

    SELECT DISTINCT DateUTC FROM [dbo].[ExampleTable] 

    Which does what I need and brings back the distinct Dates

    However I need to extend this query so it only brings back DateUTC where its greater than GT the Watermark Value. I dont know how to do this. 

    Ive used this before 

    DateUTC gt '@{activity('LookupWatermarkOld').output.firstRow.WaterMarkValue}'

    But not when I am already Creating the distinct set of dates.

    Any help would be fantastic

    Debbie


    Debbie

    Wednesday, December 18, 2019 12:23 PM

Answers

  • To pass the date from lookup output in Copy data activity - Source SQL query, you can use below syntax:

    SELECT DISTINCT DateUTC
    FROM [dbo].[ExampleTable]
    WHERE DateUTC > '@{activity('LookupWatermarkOld').output.firstRow.WaterMarkValue}'


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

    • Marked as answer by Debbie Edwards Wednesday, December 18, 2019 2:42 PM
    Wednesday, December 18, 2019 1:50 PM

All replies

  • To pass the date from lookup output in Copy data activity - Source SQL query, you can use below syntax:

    SELECT DISTINCT DateUTC
    FROM [dbo].[ExampleTable]
    WHERE DateUTC > '@{activity('LookupWatermarkOld').output.firstRow.WaterMarkValue}'


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

    • Marked as answer by Debbie Edwards Wednesday, December 18, 2019 2:42 PM
    Wednesday, December 18, 2019 1:50 PM
  • Fantastic. Thank you!

    Debbie

    Wednesday, December 18, 2019 2:42 PM