Skip to main content
To homepage To homepage

Ingest

Ask questions about Ingest Instances in TimeXtender Data Integration

  • 253 Topics
  • 1,360 Replies
253 Topics
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

36211
erik.van.mastrigt
TimeXtender Xpert
11 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)

893
kerry_bespoke
Contributor
11 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

33312
daniel
TimeXtender Xpert
11 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. 

1345
J
Contributor
11 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
11 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? 

1283
K
Contributor
11 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!

792
Christian Hauggaard
Community Manager
11 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.

864
Christian Hauggaard
Community Manager
11 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.

823
Christian Hauggaard
Community Manager
11 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
11 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

27619
Thomas Lind
Community Manager
11 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

641
daniel
TimeXtender Xpert
11 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?

1705
G
Community Manager
11 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

765
Christian Hauggaard
Community Manager
11 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?

61623
Thomas Lind
Community Manager
1 year 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?

2153
Thomas Lind
Community Manager
1 year 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.

973
Christian Hauggaard
Community Manager
1 year ago
K
Contributor
KajEmergoContributor
asked in Ingest
Using open source API with download link

In order to retrieve meteorological data from The Netherlands, we want to use the Open Data API developed by the Dutch Meteorological Institute (KNMI). This website contains all relevant information on using the API.In summary, these are the steps that we want to undertake. Anyone can do this, because of the anonymous API key that is open to public:Locate the ‘Anonymous API key’, can be found on the KNMI documentation website Determine the dataset name, version and filename to establish the correct endpoint In this example we want to use the following endpoint: https://api.dataplatform.knmi.nl/open-data/v1/datasets/etmaalgegevensKNMIstations/versions/1/files/KIS___OPER_P___OBS_____L2.nc/url Execute the GET call (make sure to use Authorization header in combination with the anonymous API key The GET call returns a download URL pointing to the KNMI Amazone AWS storage which can be easily used in Postman to retrieve the data (in x-netcdf format)Would you like to try the above in Postman a

1698
K
Contributor
1 year ago
sierd.zuiderveld
Contributor
sierd.zuiderveldContributor
asked in Ingest
Best Azure VM size for ODX

Our ODX is hosted on a separate VM in Azure. We followed the suggestion to use the size DS2_v2, but we encountered frequent “System Out of Memory exception was thrown” errors that caused the ODX to stop. We are planning to switch to E2_ads_v5, which has twice the memory, but I am curious if anyone else has faced this issue and has any advice.

1133
M
Employee
1 year ago
S
Contributor
Søren SørensenContributor
asked in Ingest
varchar(max) fields not coming through in ADLS Gen2

I have a table with a varchar(max) field (stored in Dedicated Pool, which is a data source). and it won’t come through to ADLS Gen2 (ODX, version 6284.1). I’m using the Azure Synapse provider.The longest field contains almost 4 million characters, but I’ve been unable to figure out whether this is a limitation in TX or Azure. Does anyone know?

1135
Christian Hauggaard
Community Manager
1 year ago
L
Starter
lukas.gStarter
asked in Ingest
Cannot find files in Excel Online (Sharepoint with Delegated Permission)

We use Sharepoint for multiple data sources. Connecting the Sharepoint Lists works fine and I have a OAuth for Excel Online running as well, but my data source throws an error: Cannot find file.  I need to handle multiple files according to this documentation:https://support.timextender.com/data%2Dsources%2D112/connect%2Dto%2Dexcel%2Dfiles%2Dwith%2Dexcel%2Donline%2D628 What’s the reason for this?  My App Permissions are set accordingly: My Setup looks like this:  

2199
Christian Hauggaard
Community Manager
1 year ago
andrew.gebhard
TimeXtender Xpert
andrew.gebhardTimeXtender Xpert
asked in Ingest
Nested API Call with case / if statement in RSD file

Hi community, I am trying to work with the LinkedIn API to retrieve details about UGC posts (i.e. engagements, clicks, etc.). This information is not part of the LinkedIn Marketing Solutions CDATA connector, so I am using the CDATA provider for REST 2022. I first made a call to the posts API to ‘Find Posts by Account’: Posts API - LinkedIn | Microsoft Learn. After that I need to pass each Post ID into the Organizational Entity Share Statistics API to ‘Retrieve Statistics for Specific UGC Posts’: Organization Share Statistics - LinkedIn | Microsoft Learn. The problem I am facing is that the Posts API returns two types of posts (which cannot be filtered in the API): shares and UcgPosts. Each of those types of posts needs a different URL format in the ‘Organizational Share Statistics’ API. So, I need a way to only pass one type of post into the second API. UCG posts are posts where the ID starts with 'urn:li:ugcPost:'The file RSD 1.txt works, until the first ID from the Posts API is a Ucg

1454
Christian Hauggaard
Community Manager
1 year ago
erik.van.mastrigt
TimeXtender Xpert
erik.van.mastrigtTimeXtender Xpert
asked in Ingest
SAP Table Data Source in ODX Server v21 - table filtering doesn't work properly

I’m struggling with the TimeXtender SAP Table Data Source version 18 in ODX Server v6429.1. In our current setup we use Table Name filtering like this: Please see the wildcards used. How does this translate to ODX Server v21? I have these three rules: The latter does not seem to work. I tried many variations (and did a sync data source consecutively), but when I click Select Tables under a transfer task, it only comes up with the tables matchin an equal-rule. Tables that should match a like-rule never show up: Using equal and wildcards doesn’t work either Any advice on how I should filter tables properly?

513
bas.hopstaken
TimeXtender Xpert
1 year ago
erik.van.mastrigt
TimeXtender Xpert
erik.van.mastrigtTimeXtender Xpert
asked in Ingest
ODX Server and unicode data source fields

Hi community,I have a MariaDB data source which I connect to using ODX Server (v 6346) and the CData/ADO.net connector for MariaDB v 23.0.8565.0.The source database is set to collation utf8mb4_general_ci. I noticed by copying from ODX Server to a Data Area that some special characters are slightly different than the source/ODX. I can fix this by manually changing the data type to nvarchar instead of varchar, but that is a lot of field editing.The Override Data Type function can help as well, but as we have a wide range of field lengths, this also means a lot of override rules.Is there a way to tell ODX Server that all varchar fields should be unicode/nvarchar by default (eg. based on the source database collation)?Example from ODX Server. These fields should all be nvarchar(xx) instead of varchar(xx): Implementing this idea would work for me as well :) In TX v20 Business Units, we had this option: 

1106
erik.van.mastrigt
TimeXtender Xpert
1 year ago
anders.e.jonsson
Contributor
anders.e.jonssonContributor
asked in Ingest
Error when creating ODX Notification on Critical Errors

Hi,We are unable to set up Notification on Critical Errors for the ODX Server. Both service and database for the ODX is on prem. The Exchange Server is deployed as a hybrid. TimeXtender version 6221.1This is the error message:  Setting up notification for the executions in the MDW’s and SSL’s is no problem. No authentication is required by the Exchange server.BRAnders

14116
anders.e.jonsson
Contributor
1 year ago

Badge winners

  • Certified Solutions Architect
    Dikshahas earned the badge Certified Solutions Architect
  • Certified Solutions Architect
    brett.taylorhas earned the badge Certified Solutions Architect
  • Certified Solutions Architect
    RobvdBrinkhas earned the badge Certified Solutions Architect
  • Ingest Expert
    aftabphas earned the badge Ingest Expert
  • Certified Solutions Architect
    Leo Rogemanhas earned the badge Certified Solutions Architect
Show all badges
Terms and ConditionsCookie settings