Data Sources Q&A

Dear community, Reach out to this community due to an issue with a JSON datasource connector.The situation:TimeXtender on premise (v20.10.40) using a Cdata ADO.net Provider for JSON 2021 (21.0.8011.0) in the Business Unit layer of the project. The connector uses a general account-password access for this datasource.User 1 logs on to the server TimeXtender is running on, does read data for this connector and got all the information expected.User 2 logs on to the server TimeXtender is running on, does read data and get just some information but not even related to this source. No error.Both users use the same connector with that general account to connect to this JSON datasource, but with different results.I asked for logfiles with verbosity = 3 of both users and attached are anonymized files. I am not sure how to interpret the differences in the logs.The log with the wrong results starts with a time out but playing around

Hi community,One of our customers want to report on NACE Code (see: https://nacev2.com/en). We are trying to find a (free/open) data source to map customers to the correct NACE Code.We do have the Dutch KVK/registration number and the EU VAT Number. Using one of these identifiers, we want to map the correct NACE Code.Does anyone have experience with this? 

Hi Support,Our client is using ETL Agile v20. They want to connect to Infor's datalake. Infor provide us with their Compass JDBC driver. Is it possible to import a JDBC driver into Jet to connect to their datalake? Are JDBC's supported with ETL Agile or will this be possible at all in a future release? Many thanks,Kashif

Hi all,When using the latest version of the CData Excel Online connector for extracting files from Sharepoint, we get a rather useless message after hitting the button ‘Authorize OAuth’. We are using the OAuth Grant Type ‘CODE’, meaning that we are only going to see the files that the account we are authenticating with can see on Sharepoint. The message we get after authenticating is like this: The message does not tell us what permissions we are missing, the account does have access to Sharepoint. However, it does not have access to the Graph Explorer. What kind of permissions do we need when authenticating this provider with a user account? Does it purely depend on the user account or does the App Registration require permissions as well? If so, which ones?

Is there any information on when/if the Cdata connector for Microsoft Dataverse will be available in timextender

I have a SQL Server Database that I have access to using my credentials, but need to set it up using a service account (AD Account). I have tested that this account has access to the SQL Server using the provided username/password in Power BI Desktop.Here are some screenshot of what I’m experiencing.Changed Authentication to AD Password Authentication:Changed the Encrypt connection option: It seems like the username is not transfered to the server…We are using TX 20.10.38.64TIA

Hi, We are trying to connect to a REST api and are using the JSON CData provider version 22.0.8257.0.We are connecting to two seperate datasources:Companies within Europe; Companies outside of EuropeThe problem with the second datasource is that the decimal seperator is mixed up.We tried some of the culture settings but it doesn’t seem to work.What is the best way to solve this?

Dear support,Historically, my customer is using several dozens of CData CSV or Excel file data sources.They have setup all those sources as Global Databases. Now they have to refactor file paths in all environments. Last Friday we were investigating if it was possible to retrieve the CDATA connection strings to those files by querying the repo but without success. For standard TX file data sources, it's possible to retrieve XML properties from GlobalDatabase tables but for CDATA (see picture 1), there is no obvious link to a CDataDataSourceID and even if we succeed to make this link, the connection string in the CDataDataSources table is hashed (see picture 2). Any possibility to decrypt this string or is the CData configuration stored somewhere else? Also nothing about CData connection string when we generate the project documentation =(

I have been trying for quite a while to connect to an REST endpoint requiring authentication. Have gone through the the cdata documentation and various ressources on the support site.I have working setups both in postman and SSIS Script Task, but is very keen on migrating to a more native TX setup (maintainability), will be migratning several more in the time to come.The process is as follows:Get Authentication Token (HTTP POST) The POSTed information is send as ‘x-www-form-urlencoded’ in the body part (Postman) The response is a json object, containing the token in a attribute Get Actual Data using the Authentication Token (HTTP GET with token as a url parameter)The source in this case i ArcGIS, and the documentation does not mention anything about a standardised authentication scheme.Is there a way around implementing this in any of the standardised authentication schemes, or do I need to

I am loading a table from an Oracle source with the ODX. When I load the table without an incremental selection rule, the table loads fine. But when I add an incremental rule on a datetime column (change date column in the source table) I get the error:ORA-01861: literal does not match format stringDoes the ODX server pass the correct where clause to the Oracle DB for the incremental load? Or am I doing something wrong?Any help greatly appreciated!

Hi to all,In order to improve performance of data loading from Netsuite , we were advised to use Netsuite ODBC provided connetor and use Suiteanalytics. I have download the odbc connector provided by Netsuite, and installed it on the Timextender VM, i get an succesful connection in connecting to NS, using ODBC-test connection. However i cannot acess the odbc driver in Timextender ODX, so how do i do that. Keeping in mind that the Netsuite CData connector uses Rest-calls with limits 1000 records.. so that is not feasable. thank you in advance  Peter  

Hello,I’m using the CData ADO.NET Provider for JSON 2021 and get this error message in the logg file.[HTTP|Res: 29] HTTP/1.1 200 OK, 548950 Bytes Transferred[HTTP|Res: 29] Request completed in 6828 ms.[EXEC|Page  ] Page successful: 1000 results (6844 ms) NextPage: [30000][EXEC|Messag] Executed query: [SELECT [Actualizations_id], [ActualizationType.Code],[META|Schema] Engine Invalid object name 'sys_resultset_close'[MDUL|SbMDUL] Drop BulkRows table:ErrorInfo[INFO|Connec] Executed sys_disconnect: Success: (16 ms)[INFO|Connec] Closed JSON connection The execution log file reports:Executing table [JSON].[LCSOC_EC_Actualizations]:failed with error: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

Hi! I have asked a similar question like this before. I am trying to request an API token and use that token inside an API get. I do this because a token is only valid for 23 hours. I need to give the POST a few paramters: A client ID, Client Secret, grant type and a customer ID to identify who I am.The API documentation says it uses OAUTH2.0 but I have not gotten it to work. I right now have the following code which returns the warning: The view "REST"."json" has no columns.The view is ignoredBelow my code: <api:script xmlns:api=http://apiscript.com/ns?v1 xmlns:xs=http://www.w3.org/2001/XMLSchema>

We have a REST API that we are calling and setting up pagination on, so we are using an rsd file.  We need to pass a calculated date (milliseconds since 1970) to the API call.  I think we might need to reference a stored date of our last data extraction and then calculate the proper date to pass to the REST API.  I am thinking we might do that in TX, but I am unclear how we can then pass a TX variable value in to the rsd file so we can put it into the query string of the API call.  Any pointers that can be provided would be appreciated. Thanks!

We have a new REST API data source that we are trying to connect to.  This data source takes only an API key through the Authorization: Bearer <api_token> request header.  I am using the CData ADO.NET Provider for REST 2019.  I have added the API URI and the custom header (bearer token), and even selected OAuth 2.0 and added a Callback URL of http://localhost:33333.  No matter what I do, it seems as though the API key is not being passed correctly.  The message I get in the response is "{"message":"Invalid Token"}" and "HTTP/1.1 401 Unauthorized".  I am able to connect to this data source using Postman with the Authorization: Bearer token.  Can anyone give advise on specific settings for using only an API key? Thanks, Dan

Although I'm following the steps explained on this page (https://legacysupport.timextender.com/hc/en-us/articles/360034695651-Connect-to-a-REST-API), I can't figure out what's going wrong.Situation: I have a REST API, with two endpoints: one for fetching all locations (locationId) and one for fetching all reviews per location. I need the locationId from the first request, iterate trough all locationId's and request the reviews per location.  First request is working perfectly, REST.json is loaded with all locations. For the second request, I've edited the RSD file, with an input parameter and filter (I know, the URL is mentioned twice, don't know which one is

Hi, To make sure my API will always function without manually changing the API token each period, I want to request the token and inject it into an API GET header. To do this, I need to know how to write the proper XML that can do the following: API URL: example.company.com/api/LoginRequired body: EMAIL: example@gmail.comPASSWORD: examplepassword123How do I write the poper XML inside a DSD file to request the token using the body parameters (not in the header), I cannot find the proper documentation. Thanks in advance!

I try to connect to an oracle database in TimeXtende and i get the error "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM".  I have no blanks in the datetime-fieldI have no date before 10-2-2010 and I have no dates after 22-11-2021I have several several datetime-fields like 5-3-2020 00:00:00Could the time being the problem ? How can i stil import alle records in TX ?     

I'm trying to get data from a REST api. The challenge is that the response is nested (JSON format).I did split up the seperate fields by editing the RSD file, adding something after the xPath...The result looks better than before but still all my answers are comma seperated in 1 field/row combination instead of 1 field 4 rows for example. It looks like this now:How can I make sure I only get 1 anwser per value per row? (So line 1, field q_position should be seperated to 4 rows with value 1,2,3 and 4)

For a REST endpoint we need to load incremental, how can I set up incremental columns in ODX and pass this values in the RSD file?

Is it possible to configure the default SQL connector Database Type "SQL Data Source" to use the Connection String property "ApplicationIntent=ReadOnly".   It does not appear to honor this setting as Timextender connects to the primary, not the read only replica.   We are confident in our Server side configuration as timextender is the only app we are having an issue with where an app does not connect to the read only replica when the connection property exists.  Is there another method for us to do this? Thanks, Luke

Hello, one of our customers is considering the use of TimeXtender but their IT department is not supporting ODBC. Is it possible to discover (metadata analysis) and transfer data from new datasources (SQL, Oracle, etc.) without the use of ODBC? Or can all be done using the ODX server?

How can I access the HTTP Response Headers when retrieving data from the CData Rest Connector? I need to read values from it to set up pagination in the .rsd file. In my case it contains the current page, records per page and total number of records.

I am working on a environment with TimExtender and Dynamics 365 for Operation.  First I must clear out ClientSecret and ClientAuthentication key before I can Authorize to d365.  After that i put in those keys and the session is started.  After a while the sessions is closed and I have to Authenticate myself all over again to be able to extract data form d365.   exept from logfiles> 2020-08-13T09.00.37.158+02:00 0 [Connection: 158] Executed connect: Error: AADSTS7000215: Invalid client secret is provided.Trace ID: e4747361-a17c-448d-a5b9-b2d2fadbf800Correlation ID: 3ab329da-2059-4716-8a08-7f20e7835759Timestamp: 2020-08-13 07:00:37Z. 2020-08-13T09.00.37.160+02:00 0 AADSTS7000215: Invalid client secret is provided.Trace ID: e4747361-a17c-448d-a5b9-b2d2fadbf800Correlation ID: 3ab329da-2059-4716-8a08-7f20e7835759Timestamp: 2020-08-13 07:00:37Z. ffo190j.xaat ffo190j.nna.C(ra , String )at ffo190j.nna.q(Stri

Hi, I need to extract data from an API which take dates as input values, [from date] and [to date]. And I need to extract data from this API each day. Is it possible to pass dynamic dates to the API connection based on todays date? I can't figure out how to do this.   cheers, Sturla