Skip to main content
To homepage To homepage

Ingest

Ask questions about Ingest Instances in TimeXtender Data Integration

  • 237 Topics
  • 1,273 Replies
237 Topics
anders.e.jonsson
Contributor
anders.e.jonssonContributor
asked in Ingest
Microsoft Dataverse Connection

Hi, I have created and populated a small table in Dataverse using a Power App.There are no problems accessing and reading data from the table with Power BI Desktop.But I don't really understand how to use CData's connector (Microsoft Dataverse (23.0.8749.0) 64bit). Does anyone know which fields to fill in for the authentication itself? Username and password or equivalent. BRAnders

2523
Christian Hauggaard
Community Manager
9 months ago
P
Contributor
pontus.berglundContributor
asked in Ingest
Read latest CSV file (from S3 bucket)

Hi,I have a working connection to an S3 bucket with CSV files, using the CData CSV connector.I would like to create a solution where I read only the latest file added to the bucket.Calling the stored procedure “ListFiles” (https://cdn.cdata.com/help/RVJ/ado/pg_sp-listfiles.htm) in a query table returns a table of the files, with filename and created timestamp.Is it possible to call ListFiles in an .rsd file? If so, I think it would be possible to access the created timestamp in another .rsd file using a query slicer which can be used to get the latest file.Have anyone solved this problem, or something similar? I am using the latest version, 6512.1. 

24610
Christian Hauggaard
Community Manager
9 months ago
K
Contributor
KajEmergoContributor
asked in Ingest
ADF - PostGreSQL 'queryTimeout' error

We are using the Azure Data Factory - PostgreSQL 14.0.0.0 data source connector and cannot run the synchronize task. We have selected ‘All tables’ in the data source setup but get an error in ADF:“Failure happened on 'Source' side. ErrorCode=UserErrorInvalidValueInPayload,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to convert the value in 'queryTimeout' property to 'System.TimeSpan' type. Please make sure the payload structure and value are correct.,Source=Microsoft.DataTransfer.DataContracts,''Type=System.ArgumentException,Message=Object of type 'System.Int64' cannot be converted to type 'System.String'.,Source=mscorlib”The error seems to be related to the ‘Command timeout’ setting in the TX portal, which we set to 1440 but ADF wants it to be a ‘System.TimeSpan’ type?When we want to use ‘Select tables’ on the ODX data source the screen runs ‘Search tables’ for a split second and then the results remain empty, no tables appear at all.The Linked Ser

672
K
Contributor
9 months ago
Paul McLeod Bespoke Analytics
TimeXtender Xpert
Paul McLeod Bespoke AnalyticsTimeXtender Xpert
asked in Ingest
V20 ODX loosing Meta Data for REST data source

I have a rest interface loading via ODX in V20 into an Azure Data Lake.Recently an infrastructure change to the server made the Rest Endpoint timeout (it has an IP whitelist and the server IP was removed).This resulted in the Synchronization task determining that there were no tables available to load, even though there are multiple custom RSD files that have been generated and the CData Rest driver has been configured to Never generate Schema files. The IP has been restored to the whitelist and the Rest interface is working again (after multiple days of the ODX loading nothing). Unfortunately the Meta Data for every table has been flagged as invalid. When I run the ODX load every table comes back with an error that the table has already been used by a table with a different GUID. And If I run the synchronize task on the ODX it deletes the mapping of every table connected to the particular ODX source (40+ tables).I cannot even run a preview of the data from the Gen 2 storage through Ti

494
Paul McLeod Bespoke Analytics
TimeXtender Xpert
9 months ago
O
Explorer
ofirbExplorer
asked in Ingest
ODX in faulted state

Hello,Our ODX gets stuck whenever we make a request to it (be it synch, transfer, or viewing a log). Then we see the error message in attachment and usually we see a second ODX all of the sudden. Any idea what may be causing it? 

1544
rory.smith
TimeXtender Xpert
9 months ago
M
Visitor
magnus.jorstadVisitor
asked in Ingest
ODX Server timestamp

Is there a way to get the timetamp of when a table was transferred from source to ODX Server? Like the DW_Timestamp. The only system field i can find is odx_batch_number. 

1465
Thomas Lind
Community Manager
9 months ago
P
Contributor
pontus.berglundContributor
asked in Ingest
SQL Database cleanup tool for ODX

Is there a way to remove tables from ODX storage that have been deleted in the desktop, similar to the SQL Database cleanup tool available in the DW? My use case is when SQL Storage is used for the ODX.The “Remove unused object” option does not drop tables it seems. The Storage Management Task is good of course, but we can’t use it for deleted data sources.Let’s say I have transferred a large table hundreds of times into my ODX and then deleted the data source. Can I remove them without having to drop the tables manually in SSMS?Best,Pontus 

1023
P
Contributor
9 months ago
bas.hopstaken
TimeXtender Xpert
bas.hopstakenTimeXtender Xpert
asked in Ingest
PK table in ODX database is growing rapidly in terms of disk space

Hi community,I’ve a question about the ‘PK’ table that is created in the ODX when you enabled the ‘Handle primary key updates’ and ‘Handle primary key deletes’.I’ve noticed the ODX database (SQL Database) is growing rapidly in disk space after enable an incremental load schedule to reload data every 5 minutes. When I used the default SQL ‘Disk usage by top tables’ I noticed the PK tables are the biggest in terms of disk space.My PK table of the GL Entry table contains 4.5 billion rows! And is 95 GB. While my DATA table contains only 118 million records.When I query the PK table and filter the primay key in this table on 1 value. This value is saved 75  times in this table. For every odx_batch_number (odx_batchnumber 0 – 74). Is this normal? I think it is a bit strange that my PK tables are the biggest tables in the ODX in terms of disk space.Even when I run the storage management task. It doesn’t clean the PK table’s. It always contains the primary key for every odx batch load.The cust

2449
rory.smith
TimeXtender Xpert
9 months ago
erik.van.mastrigt
TimeXtender Xpert
erik.van.mastrigtTimeXtender Xpert
asked in Ingest
Loading Excel files from OneLake

Hi,I’m trying to load an Excel file from a OneLake. As OneLake is a rebranded version of Data Lake Gen2, my assumption is that it should be possible to use existing CData features to load data.I have a working setup where we use CData’s Excel provider (version 23.0.8585.0) to load an Excel sheet from a Data Lake Gen2 I explicitly configured the connection type as ADLS Gen2. Actually I was hoping that this configuration would work after changing the [Azure Storage Account] and [URI] to the OneLake parameters, but unfortunately that doesn’t work Error message:  Looking into the logs, I see that CData tries to connect to *.dfs.core.windows.net. However, the uri of OneLake is *.onelake.dfs.fabric.microsoft.com. Manually entering the abfss:// uri did not work here Is it correct that CData is not yet compatible with OneLake, even though techniques under the hood are similar? Documentation on https://cdn.cdata.com/help/RXJ/ado/default.htm does not mention it (yet).Did anyone manage to load da

35211
erik.van.mastrigt
TimeXtender Xpert
9 months ago
sierd.zuiderveld
Contributor
sierd.zuiderveldContributor
posted in Ingest
For "Add New Table" or the "Add New Table with Field Selection" option, the fields are either randomly arranged or aligned with the source database.

We have observed an inconsistency in the field order when adding a new table from the ODX to the DSA. Depending on whether we use the "Add New Table" or the "Add New Table with Field Selection" option, the fields are either randomly arranged or aligned with the source database.When we use “Add New Table” we notice that all fields are somewhat randomly arranged. When we use “Add New Table with Field Selection” and then check all the boxes the order is exactly as in the source database (as expected).  How come that there is a difference? Is this on purpose? Thanks (TimeXtender 6346.1)

883
kerry_bespoke
Contributor
10 months ago
O
Explorer
ofirbExplorer
asked in Ingest
Incorrect syntax near the keyword 'ORDER'

Hello,We’re getting the following error when executing a table in DSA.ODX object synchronization detects no changes, and our ODX source is synched as well.I’m guessing the ORDER is an order by… Any idea what causes it? Thanks

32412
daniel
TimeXtender Xpert
10 months ago
J
Contributor
jyothi.dosiContributor
asked in Ingest
Extract Data from D365 to datalake

Hi, Please can you suggest on the following : Export to Data Lake is depreciated by Microsoft in Oct 2024 this year. So, what is the best alternate solution to extract the data from ERP FO solution for reporting? How to extract data from Field Service & CE ERP solution? OData connector is not good option as it is very slow & I cannot build incremental load on this. 

1315
J
Contributor
10 months ago
E
Starter
erwin.pasStarter
asked in Ingest
Window function not correctly validation on lookup fields

Hi, I created a field StorageReportIndicator in a table to rank the records in the table. This rank is a SQL window function RANK which combines table fields initially loaded from the data source (StorageUnit and ReferenceDateTime below) and lookup fields via table joins (DSA.Datum table, fields WeekKey and DayOfWeek).  Goal is to have a rank to determine the first record of each week for each storageUnit. The outcome afer execution is that all records have rank 1 which is incorrect. I verified this with the same SQL statement in Management Studio.Could it be that this window function does not have the right result since the lookup is done after the rank is calculated? Note, the table is not historical and incremental. Anyone with experience with this case and is there a proper solution?

553
E
Starter
10 months ago
K
Contributor
KarankadamContributor
asked in Ingest
Date Range Error

As shown in Figure Syncronize Task is sucessfully completed, but Transfer Task gets an below error “System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 ”PM even though my date range starts from 2002 and ends at 2023.Even though after suggestion by @Christian Hauggaard i have made changes in setting of Date Conversition and selected MS SQL Min/Max option, but once i change the setting then i gets the below error for same process,“System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out”(This error is geeting for those tables who has Date Column, and date column has such data (for example 03/01/2022 6:42 PM ) )​​​​​​​What are steps to solve above error? 

1273
K
Contributor
10 months ago
G
Starter
gdlc_esculineStarter
asked in Ingest
System.AggregateException: The column X with id X does not exist in the ODX

Hi all, I have an data source that gets daily refreshed in the ODX. With some tables, I have encountered the following error. Does anyone knows how I can resolve this? Thanks in advance!

782
Christian Hauggaard
Community Manager
10 months ago
K
Contributor
KarankadamContributor
asked in Ingest
“Transfer Data from Data Sourse with Incremental Load”

While Running Transfering Task im getting error “Transfer Data from Data Sourse with Incremental Load”after appling incremenatl Load, again im getting Same error.

854
Christian Hauggaard
Community Manager
10 months ago
K
Contributor
KarankadamContributor
asked in Ingest
No tables are visible under Select Table Option

In query table all tables are visible but while adding table in MDW(In select table option ) no tables are visible.

783
Christian Hauggaard
Community Manager
10 months ago
K
Contributor
KarankadamContributor
asked in Ingest
Invalid data type long raw

The column LVALUE from the table "PROD"."MICROSOFTDTPROPERTIES" failed synchronization with the error:System.Exception: Invalid data type long raw.how to solve this error. 

873
Christian Hauggaard
Community Manager
10 months ago
A
Contributor
aftabpContributor
asked in Ingest
Paginated RSD file

I have a API endpoint with pagination:https://www.mytestserver,com/DWH/api/v1/all-buildings?buildingType=BAR&page=5I have created a RSD file with the following code after  </api:info>  <api:set attr="in.Header:Name#" value="Accept"/> <api:set attr="in.Header:Value#" value="application/json"/> <api:set attr="in.DataModel" value="FLATTENEDDOCUMENT"/> <api:set attr="in.JSONPath" value="$.data"/> <api:set attr="in.EnablePaging" value="TRUE"/> <api:set attr="in.Page" value="1"/> <api:set attr="in.PageCount" value="100"/> <api:set attr="in.BaseURI" value="https://www.mytestserver.com/DWH/api/v1/all-buildings?buildingType=BAR&page={page}" /> <api:set attr="in.ElementMapPath#" value="/json/meta/pagination/pages"/> <api:set attr="in.ElementMapName#" value="pages"/> <!-- The GET method corresponds to SELECT. Here you can override the default

27019
Thomas Lind
Community Manager
10 months ago
K
Contributor
KarankadamContributor
asked in Ingest
Tables are not Visible in Select Table Option while adding data from ODX to MDW

If you see Task are sucessfuly completed in ODX, and tables are vissile in query tool, but while loading data from ODX to MDW in select Table options tables are not visible, as shown in Figure

631
daniel
TimeXtender Xpert
10 months ago
K
Contributor
KajEmergoContributor
asked in Ingest
Connection issue ODX Server upgrade

When launching the newly installed ODX Server configuration (20.10.45), we get the following error:First, we thought this was a Firewall issue on our (Azure VM), because this test-netconnection did not work (see the second one failing):When I try to reach that second address from my local laptop, I get the same failure. Could you explain what is going on here? When using the ‘tracert’ to timextender-api.azurewebsites.net command in the command prompt on my local machine I see that it forwards to waws-prod-am2-061.cloudapp.net. When we try to do the same on the TX Azure VM, it times out on the first step. Do you have any advice on how to proceed here? Could it still be a Firewall issue on our VM?

1695
G
Community Manager
10 months ago
D
Contributor
dsvartzmanContributor
asked in Ingest
Error 503 (ODX)

hi, Lately were are getting alot of 503 exceptions.  Error message is typically: The task '983693' failed:System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.Net.Http.HttpRequestException: Response status code does not indicate success: 503 (The server is busy.).   at System.Net.Http.HttpResponseMessage.EnsureSuccessStatusCode()   at Azure.Management.REST.RestWebRequestManager.<SendRequest>d__6.MoveNext()--- End of stack trace from previous location where exception was thrown ---   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)   at Azure.Management.REST.RestWebRequestManager.<SendRequest>d__5.MoveNext()--- End of stack trace from previous location where exception was thrown ---   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()   at System.Runtime.CompilerSe

745
Christian Hauggaard
Community Manager
10 months ago
P
Contributor
pontus.berglundContributor
asked in Ingest
OAuth with authorization prefix "Bearer "

Hi,I am trying to connect to a REST API that uses OAuth2 authentication.The API documentation is very clear on what to include in the authentication:  I have entered all this information in the REST CData provider and I can successfully use the provider’s “Authorize OAuth” button.However, when I try to test the connection, error “401 - Unauthorizated” is returned.In the API documentation, they state that the token needs a prefix “Bearer “.I suspect my error is caused by the REST CData provider sending the authorization header without the Bearer prefix, like so:Authorization: {ACCESS_TOKEN}Which the API of course does not accept. Is there any way to include a prefix to the token in the authorization header?

59523
Thomas Lind
Community Manager
11 months ago
rvgfox
Problem Solver
rvgfoxProblem Solver
asked in Ingest
Azure Active Directory data source conector

Hi, can you provide me with detailed instructions on how to set it up?

2143
Thomas Lind
Community Manager
11 months ago
K
Contributor
KajEmergoContributor
asked in Ingest
Sharepoint data source 'Authorize OAuth' not working

When configuring the CData Microsoft Sharepoint (23.0.8669) provider for extracting a Sharepoint List, we encounter a problem with the button ‘Authorize OAuth’. We are configuring the data source in ODX Server 20.10.40 and TX version is also 20.10.40.The following settings are configured:According to the CData documentation AzureAD should be used as Auth Scheme to leverage OAuth to authenticate, we also use this exact same setting and the same app registration for another data source using the Excel Online provider for which all is ok.However, when using ‘Authorize OAuth’ with the Sharepoint provided, we get the following error:What could be causing this? Changing the file or location for OAuth does not matter at all, we have also tried other versions of the provider but they all give this error. Also, changing Initiate OAuth to ‘REFRESH’ does not have an effect.

963
Christian Hauggaard
Community Manager
11 months ago

Badge winners

  • Certified Solutions Architect
    pkaustubhahas earned the badge Certified Solutions Architect
  • Ingest Expert
    avanrijnhas earned the badge Ingest Expert
  • Deliver Expert
    danielhas earned the badge Deliver Expert
  • Jobs & Executions Expert
    p.ruijterhas earned the badge Jobs & Executions Expert
  • Certified Solutions Architect
    lstegmeierhas earned the badge Certified Solutions Architect
Show all badges
Terms and ConditionsCookie settings