Skip to main content
To homepage To homepage
Solved

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

  • February 13, 2024
  • 7 replies
  • 140 views
K
Contributor
Forum|alt.badge.img
  • KCMT
  • Contributor
  • 33 replies

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

 

 

Best answer by KCMT

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.

View original
Did this topic help you find an answer to your question?

7 replies

Christian Hauggaard
Community Manager
Forum|alt.badge.img+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
Contributor
Forum|alt.badge.img
  • KCMT
  • Author
  • Contributor
  • 33 replies
  • Answer
  • February 14, 2024

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
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • 1068 replies
  • February 14, 2024

Hi @KCMT 

And you added this as a managed query table.

Can you confirm that it works?

K
Contributor
Forum|alt.badge.img
  • KCMT
  • Author
  • Contributor
  • 33 replies
  • February 14, 2024

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
TimeXtender Xpert
Forum|alt.badge.img+7
  • daniel
  • TimeXtender Xpert
  • 192 replies
  • February 14, 2024

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
Contributor
Forum|alt.badge.img
  • KCMT
  • Author
  • Contributor
  • 33 replies
  • February 14, 2024
daniel wrote:

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
1 person likes this
  • daniel
    daniel
daniel
TimeXtender Xpert
Forum|alt.badge.img+7
  • daniel
  • TimeXtender Xpert
  • 192 replies
  • February 14, 2024

@KCMT , 
Great minds think alike ;)

Reply


Most helpful members this week

Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings