To homepage To homepage
Solved

Extracting data from External-database

N
Rank

Hi TX Community!

I get data from an external-database everyday.
Since we have an aim of incremental-loading the data that we extract, we are doing some query-tables from the datasource to create a “incremental-load”-key.

In that matter we are experiencing two issues:

1- TX cannot read the date-formats that are extracted from the database. This is the format that we get out:

 


Of course, we can right-click on the field and edit the datatype, but that we would have to do everytime we syncronize the datasource because everytime we syncronize the datasource all the date-fields are back to the “unknown”-format and therefore we would have to right-click on each field and edit the data-type. We’ve tried to use the “Data type overrides” but it doesnt seem that we can convert from an “unknown”-format. How can we solve this problem? As mentioned, the tables are query tables and therefore we would like to think that the date-formatting could be solved with a CAST or a CONVERT function. Any ideas?

2- In each query-table we are creating an “incremental-key” (which is also the field name). The field logic is very simple:

“case when lastModified is null then created else lastModified end as incdate”

The field is created but when we try to execute the table we get the following error on every table that has the “incdate” field:

 


Looking forward to hearing from you!

​​​​​​​Ismail​​​​​​​

icon

Best answer by daniel 19 May 2023, 17:03

View original

7 replies

anders.e.jonsson
Rank
Userlevel 1
Badge +1

Hi @nohikh 

A couple of questions;

  1. Which version of TimeXtender are you using?
  2. Can you please provide some examples of the contents in your date columns.?
  3. What does the code in your query tables look like?

BR

Anders

Anders
N
Rank

Hi @anders.e.jonsson 

Thanks for your feedback :) 

1. I am using TimeXtender version 20.10.14.64
2. Two of the variables, that are already present from the data source are “created” and “lastModified”. Again, both of them are coming out from the datasource as “unknown”-format. 

Here is an example from a table called “CampaignPeriod”:

 


And the reason I am doing a query-version of this table is that I need to create a third date-field based on these two date-variables.
Therefore I create “CampaignPeriod_Q” which contains the new date-field, that is going to be my incremental-key (btw. the distinct length of “incdate” is 19 on ALL rows)

 

  1.  Here is how I create the “campaignPeriod_Q” table:
     

    SELECT *,
    'RDR' as Company,
    case when lastModified is null then created else lastModified end as incdate

    FROM `daa`.`CampaignPeriod`

    And as mentioned, I am able to preview the data in Campaign_Q but I cant execute it due to 2) in my first post AND I have to change the data-type manually due to 1) in my first post



    A little more context:
    I have set up a global database where I have defined the connection-properties  by using “MySQL Data Provider”:


    And I am getting the data by looking at that database from my STG-environment.

    Hope this was helpful!
    Looking forward to your feedback.

    BR
    Ismail

anders.e.jonsson
Rank
Userlevel 1
Badge +1

Hi @nohikh ,

The first issue, TX isn’t recognizing the data type of your date fields. I assume that it’s a date on the form yy-mm-dd. If so, Maybe you could solve that by adding the missing two numbers and doing a cast in the query for the query table. Like this:
lastmodified = CAST( CONCAT( '20', CAST( lastmodified AS varchar(17)) ) AS datetime)

I think the second one is trickier . If I’m not mistaken the ODX will only accept fields that actually exists in the source when setting up the rules for incremental load. That is the fields it pulls when the sync is done.

I suppose you don’t have any control over the source. If you do, the easiest way would probably be to create a view in it and do all that’s needed there. Fixing the datatypes as well as creating a field to use in the rule for incremental load.

BR

Anders

Anders
daniel
Rank
Userlevel 4
Badge +6

Hey @nohikh 

  1. Unfortunately I do not ahve a straight answer for this one. What connector are you using. Perhaps using a different one could fix the issue. Wat type of database is the external data base? I’ve had some good results by switching connector (either CDATA or not) or downling the driver from the vendor of the database and using a ODBC connection (usually there is some more documentation on the vendor driver or you could ask the vendor for help perhaps)
    1. 1 As i’m thinking more about it: Could you create a query table where the data is correct? If casting doenst work, maybe you can try converting or formatting the date (so try and use CAST(), CONVERT() or FORMAT().)
      If your getting error’s on the NULL values, maybe try it with the TRY_CAST or TRY_CONVERT. Ofcourse this wll only work if the exteral database is a SQL DB.
  2. For the case when lastModified is null then created else lastModified end as incdate you could use a coalesce? Might be a sweeter way then the case.
    COALESCE(    lastModified ,created ) AS incdate 

Hope this helps

= Daniel

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @nohikh which data source are you using in your business unit? 

Can you please share a screenshot of your data source settings similar to below?

 

N
Rank

Hi all

Thanks to all of you for your feedback!

My issue with the strange data-types was resolved after I changed my data source to “CData ADO.NET Provider for My SQL”. I was using an old version of the MySQL connector.

Regarding the issue about using a query-table column as an incremental-key, I did not manage to find a solution yet. However, I’ve contacted the data-owners and told them to implement the field in their tables such that it will be available for me in the source-data.

BR
Ismail

rory.smith
Rank
Userlevel 6
Badge +7

Hi @nohikh ,

 

good to hear you solved the typing issue with a better connector - as some additional info:

as far as I am aware Query Tables run on the source system using the data types of the source system. This means that new fields you create should be explicitly casted/converted to a type you know SQL Server can handle to avoid getting the unknown types. Some DBMS have very different types and require explicit casting to string and subsequent conversion in SQL Server.

Reply


Terms and ConditionsCookie settings