Dynamically pass table name to a SQL query in ADF RRS feed

  • Question

  • I need to create a solution using ADF to compare Dev & Prod environments(all tables) on Azure SQL Server (different for Dev & Prod environments) and highlight the table differences.
    I am using sys.objects to read the list of all tables in Lookup activity(First lookup activity),then pass each table name to ForEach activity wherein second Lookup transformation would read record counts against that table in Prod & Dev environments and compare the record counts between them, finally performing some activity.
    I want to know how can I pass each table name read in First Lookup activity to second lookup activity inside ForEach Loop ?

    Eg. Outer Lookup activity reads tables A, B, C, D and pass each table name one at a time inside ForEach wherein another lookup would execute something like below query in Prod and Dev environments :

    Select count(*) from A ---in first iteration
    then Select count(*) from B ---in second iteration
    Select count(*) from C ---in third iteration and so on.
    Saturday, May 23, 2020 10:52 AM