To homepage To homepage
Solved

How to set up Source based incremental load with seperate Date and Timestamp column

  • 13 February 2024
  • 7 replies
  • 79 views
K
Rank
Userlevel 2

Edit

Offset value in incremental rule is not available for my Tx version I suppose. UpdateDate is a DateTime column and my UpdateTS an Integer. I’ll see if I can combine them in a single field somehow in the backend.

TX20.10.35.64
I try to set up incremental loading with a separate Date & Time column but can't seem to find the right way. Found an older source below indicating it's not possible, since it results in a OR clause instead of a AND clause.

I am also not able to set up a distraction on my date column to say UpdateDate - 1 day for some reason. Is there another way to do this in the meantime instead of only the date column?

Is source based incremental load possible on separate date and time fields? – TimeXtender Support

 

 

icon

Best answer by KCMT 14 February 2024, 10:54

View original

7 replies

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @KCMT 

Can you please try to create a single date time field (concatenating the date field and the timestamp) using a query table? 

K
Rank
Userlevel 2

Hi @Christian Hauggaard 

Since my UpdateDate is a DateTime field only showing dates and my UpdateTS fields is an Integer I had to do some more transforming to create a DateTime field. Based on the below source I was able to create a datetime field out of it.

datatypes - How to combine date and time to datetime2 in SQL Server? - Database Administrators Stack Exchange 

DATEADD(DAY, DATEDIFF(DAY, '19000101', UpdateDate), CAST(CONVERT(TIME, STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6), UpdateTS), 6), 3, 0, ':'), 6, 0, ':')) AS DATETIME2(7)))

I added this field so the view in my source database.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @KCMT 

And you added this as a managed query table.

Can you confirm that it works?

K
Rank
Userlevel 2

Hi @Thomas Lind 

I didn't use a managed query table so can't confirm unfortunately.

I integrate 20+ SAP Databases from the same SQL server, which at the time of starting up resulted in really slow DSA loading times due to having 20+ separate data sources. Hence, I created a _TX_*TableName* view on the Server for every source table which UNIONs all the 20+ Databases. This way, I only have 1 datasource for all these 20+ databases.

The views are managed by an Excel template I created where I can automatically update all tables when I want to add a column. I added this column to the Template and altered all the views in bulk.

Seems to be working fine.

daniel
Rank
Userlevel 4
Badge +5

Dear @KCMT ,

If you have a seperate SQL server with a logic to UNION all the tables, why don't you add a column with that transformation in that SQL statement? So now you have that column fixed in your source table?

K
Rank
Userlevel 2

Dear @KCMT ,

If you have a seperate SQL server with a logic to UNION all the tables, why don't you add a column with that transformation in that SQL statement? So now you have that column fixed in your source table?

Hi @daniel 

That's indeed exactly what I did ;) The transformation column in included in the view.

daniel
Rank
Userlevel 4
Badge +5

@KCMT , 
Great minds think alike ;)

Reply


Terms and ConditionsCookie settings