Skip to main content
To homepage To homepage
SUBMITTED

Query Table and Incremental load with subtraction for ADF SQL data source

  • May 8, 2023
  • 5 replies
  • 78 views
K
Contributor
Forum|alt.badge.img+2

Hi,

We are pulling data from a SQL data source with ADF and would like to enable incremental load in our Prepare instance. Unfortunately, the data source does not have ideal characteristics for incremental loading:

  • It has a ModifiedDate column which is properly filled
  • In a separate ModifiedTime column, varchar(5), which contains the modified timestamps in HH:mm format
  • Primary key is also available

ADF does not detect possible rules for incremental loading as a result of the above. We wanted to create a single ModifiedDateTime column with a query table, but that is not possible on an ADF source. I also adressed this in another topic.

How do you think we should handle this?

5 replies

Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • May 8, 2023

Hi Kaj

That is a tough question. I assume you are using one of the old releases and not the new version like 6221.1.

If you were, you could do a subtraction by one day on the ModifiedDate field and make it able to use incremental load there.

My best suggestion is to set up incremental load on the ModifiedDate field and then run a full load once a day or week depending on how often you run the table. This is how we would have done it in the past to resolve this.

K
Contributor
Forum|alt.badge.img+2
  • KajEmergo
  • Author
  • Contributor
  • May 8, 2023

Hi Thomas,

That is correct, we are on 20.10.40 so TimeXtender Classic. 

I am afraid your solution is not gonna do the job in our situation since we extract data from the source multiple times a day.

A query table would help us in this case, but this is not possible on an ADF source. Do you know why? Is this going to be possible in the future?

Best regards,

Kaj

Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • May 8, 2023

Hi Kaj

Once we have an upgrade tool to the TimeXtender Data Integration (i.e. v 6.xxx) it will be possible to implement incremental load with subtraction, but I do agree it would be a nice feature to be able to do query tables in ADF.

I am not sure why it was not added, perhaps it was not possible to do anything but copy, but you would have to do a pre-step that could merge the fields first.

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

Hi @KajEmergo I will convert this question to an idea 

    K
    Contributor
    Forum|alt.badge.img+2
    • KajEmergo
    • Author
    • Contributor
    • June 20, 2024

    @Christian Hauggaard or @Thomas Lind, is there an update regarding this idea? The query table or incremental load with subtraction are both not available for ADF data sources in the latest ODX Server release. 

    Terms and ConditionsAccessibility statement