Skip to main content
To homepage To homepage

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

 

 

Hi @KCMT 

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

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.

Hi @KCMT 

And you added this as a managed query table.

Can you confirm that it works?

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.

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?

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.

@KCMT , 
Great minds think alike ;)

Reply


Terms and ConditionsCookie settings