To homepage To homepage
Solved

Paginated RSD file

  • 14 December 2023
  • 19 replies
  • 223 views
A
Rank

I have a API endpoint with pagination:

https://www.mytestserver,com/DWH/api/v1/all-buildings?buildingType=BAR&page=5

I 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 processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
	<api:script method="GET">
		<api:set attr="in.URI" value="[in.BaseURI | replace('{page}', 1)]"/>
			<api:call op="jsonproviderGet" in="in" out="out">
			<!-- <api:set attr="in.PageCount" value="[out.pages]"/> -->
	</api:call> 

	<api:enum range="1..[in.PageCount]">
		<!-- <api:set attr="in.Page" value="[_value]"/> -->
		<api:set attr="in.URI" value="[in.BaseURI | replace('{page}', [in.Page])]"/>
		<api:call op="jsonproviderGet" in="in" out="out">
			<api:push item="out"/>
		</api:call>
	</api:enum>
  </api:script>

There couple of things i am wondering about:

  • I dont know how many pages there are. Is there anyway to get around defining page count?
  • Is there anything wrong about assigning a value to PageCount higher than the actual pages. Lets say max count is 5 but i put 50 because i dont know many pages are there in tofal but they are likely to increase. 
  • At the moment below two lines are commented out:
<api:set attr="in.PageCount" value="[out.pages]"/>
<api:set attr="in.Page" value="[_value]"/>

When use the RSD-file without the commenting out the lines i get the following error.

I have followed the guide here, so why i am getting error: Advanced RSD file options | Community (timextender.com)

[500] Could not execute the specified command: Formatter [ pages ] failed in the evaluation of [out.pages]. The error was:
The value of the attribute could not be accessed: The attribute does not exist.

Details:

Formatter [ pages ] failed in the evaluation of [out.pages]. The error was: ...
Module: System.Data.CData.REST
dwo220E.EgJ
   at dwo220E.ET.n()
   at dwo220E.EwK.a(RSBOperation , EIr )
   at dwo220E.EIr.D(Egc , EJS , OEz , EJS , EJS , String )
   at dwo220E.EIr.r(RSBOperation , EJS , OEz , EJS , EJS )
   at dwo220E.EuJ.Q(String , RSBOperation , RSBItem , RSBCallback )
   at dwo220E.EuJ.Call(RSBOperation , RSBItem , RSBCallback )
   at dwo220E.FrV.k(RSBContext , EcB`1 , Object , RSBItem , RSBCallback )
   at dwo220E.FrI.y(RSBContext )
   at dwo220E.FrI.Exec(RSBContext )
   at dwo220E.EwK.L(RSBOperation , EuJ )
   at dwo220E.EwK.a(RSBOperation , EIr )
   at dwo220E.EgB.Q(EgB , EIr )
   at dwo220E.EgB.se(EIr )
   at dwo220E.Ewh.v(EIr )
   at dwo220E.Ewh.o(Ewh , EIr )
   at dwo220E.Ewh.se(EIr )
   at dwo220E.Ewh.v(EIr )
   at dwo220E.Ewh.o(Ewh , EIr )
   at dwo220E.Ewh.se(EIr )
   at dwo220E.Ewh.se(EIr )
   at dwo220E.EJm.VI(EIr )
   at dwo220E.EwE.ff(EIr , Boolean )
   at dwo220E.EIr.D(Egc , EJS , OEz , EJS , EJS , String )
   at dwo220E.EIr.h(String , EJS , OEz , EJS , EJS )
   at dwo220E.EuJ.Call(String )
   at dwo220E.xJ.N(RSBContext , xI )
   at dwo220E.OVr.Yn()
   at dwo220E.OVw.Yn()
   at dwo220E.OEh.o(rg , yE )
   at dwo220E.OFO.xI()
   at dwo220E.OEh.fA()
   at dwo220E.xm.k(SQLQuery , Int32 , yL )
   at dwo220E.xm.x(IDataStatement , SQLQuery , QueryParameterCollection , Int32 , String , EnO )
   at dwo220E.OVd.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at dwo220E.dP.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at dwo220E.EKd.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at dwo220E.hw.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at dwo220E.BB.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at dwo220E.OSO.xY()
   at dwo220E.OSK.cu()
   at dwo220E.LS.cI(QueryParameterCollection )
   at System.Data.CData.REST.RESTCommand.ExecuteDataReader(CommandBehavior behavior)

[500] Could not execute the specified command: Formatter [ pages ] failed in the evaluation of [out.pages]. The error was: ...
Module: System.Data.CData.REST
System.Data.CData.REST.RESTException
   at System.Data.CData.REST.RESTCommand.ExecuteDataReader(CommandBehavior behavior)
   at System.Data.CData.REST.RESTCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at TimeXtender.DataManager.StepTransferSSISExecute.BulkCopySource(Boolean executeIncrementalLoad, DataSource dataSource, VariableResolveObject dynamicResolveObject)
   at TimeXtender.DataManager.StepTransferSSISExecute.DoStageDataSource(Boolean isIncrementalAllowed, DataSource dataSource, StepSetup stepSetup, VariableResolveObject dynamicResolveObject, Boolean loadPrimaryKeysOnTransfer)
   at TimeXtender.DataManager.StepTransferSSISExecute.DoStage(StepSetup stepSetup, VariableResolveObject dynamicResolveObject)

 

icon

Best answer by aftabp 4 January 2024, 16:03

View original

19 replies

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @aftabp 

Do you have also have a page size variable in the API along with the pages and page options?

What can you see in the log? If you haven’t got one, please set one up, it should show you what part is failing.

<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 processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
<api:script method="GET">
	<api:set attr="in.URI" value="[in.BaseURI | replace('{page}', 1)]"/>
	<api:call op="jsonproviderGet" in="in" out="out">
		<api:set attr="in.PageCount" value="[out.pages]"/>
	</api:call> 

	<api:enum range="1..[in.PageCount]">
		<api:set attr="in.Page" value="[_value]"/>
		<api:set attr="in.URI" value="[in.BaseURI | replace('{page}', [in.Page])]"/>
		<api:call op="jsonproviderGet" in="in" out="out">
			<api:push item="out"/>
		</api:call>
	</api:enum>
</api:script>

I think it looks correct, I can’t see what it is that is missing.

My best guess is that it is the data model or the JSONPath that is giving issues.

You can try to uncomment the first call to get the max page and just use the PageCount equal 100 to see if that is what is failing.

A
Rank

@Thomas Lind 
Verbosity is set to 3

 

In log i can see that Deploy and Execute reads data. I can see lots of data from API endpoint. At the end of the last line the following error occurs:

 

2023-12-15T10:07:23.639+01:00	2	[17|Q-Id]	[HTTP|Res: 6] HTTP/1.1 200 OK, 5180330 Bytes Transferred
2023-12-15T10:07:23.639+01:00	2	[17|Q-Id]	[HTTP|Res: 6] Request completed in 13047 ms.
2023-12-15T10:07:23.639+01:00	1	[17|Q-Id]	[EXEC|Messag] Executed query: [SELECT	[buildingID],
/* rest of the fields */
.
.
.
.
.
FROM [REST].[allBuildings]] Success: 0 results (13047 ms)
2023-12-15T10:07:23.655+01:00	0	[17|Q-Id]	[SQL |Messag] Query Failed: [SELECT	[buildingID],
/* rest of the fields */
.
.
.
.
FROM [REST].[allBuildings]]. Error: Formatter [ pages ] failed in the evaluation of [out.pages]. The error was:
The value of the attribute could not be accessed: The attribute does not exist.
2023-12-15T10:07:23.655+01:00	0	Formatter [ pages ] failed in the evaluation of [out.pages]. The error was:
The value of the attribute could not be accessed: The attribute does not exist.

 

Thomas Lind
Rank
Userlevel 6
Badge +5

Try to set the DataModel option to DOCUMENT and see if that makes a difference.

A
Rank

Try to set the DataModel option to DOCUMENT and see if that makes a difference.

Setting to DOCUMENT didnt help.

With the code above i am only getting top 20 records 

A
Rank

Hi @aftabp 

Do you have also have a page size variable in the API along with the pages and page options?

What can you see in the log? If you haven’t got one, please set one up, it should show you what part is failing.

<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 processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
<api:script method="GET">
	<api:set attr="in.URI" value="[in.BaseURI | replace('{page}', 1)]"/>
	<api:call op="jsonproviderGet" in="in" out="out">
		<api:set attr="in.PageCount" value="[out.pages]"/>
	</api:call> 

	<api:enum range="1..[in.PageCount]">
		<api:set attr="in.Page" value="[_value]"/>
		<api:set attr="in.URI" value="[in.BaseURI | replace('{page}', [in.Page])]"/>
		<api:call op="jsonproviderGet" in="in" out="out">
			<api:push item="out"/>
		</api:call>
	</api:enum>
</api:script>

I think it looks correct, I can’t see what it is that is missing.

My best guess is that it is the data model or the JSONPath that is giving issues.

You can try to uncomment the first call to get the max page and just use the PageCount equal 100 to see if that is what is failing.

How do i set up a log ?

A
Rank

@Thomas Lind 

So here is an update. 
After rewriting the code (using if) and setting ElementMapPath to /json/data i can see that TX only reterives top 20 rows. So Thomas, JSON path is correct but may be ElementMapPath is wrong !

I have contacted the API provider and hopefully i will get some answers soon :)

 
<api:set attr="in.DataModel" 							 value="FLATTENDOCUMENT" />
  <api:set attr="in.EnablePaging" 						 value="TRUE" />
  <api:set attr="in.startPage"                           value="1" />
  <api:set attr="in.BaseURI" 							 value="https://www.mytestserver.com/allBuildings?buildingType=BAR&page={pageNumber}" />
  <api:set attr="in.JSONPath" 							 value="$.data" />
  <api:set attr="in.hasNextPage" 						 value="TRUE" />
  <api:set attr="in.ElementMapPath#" 					 value="/json/data/"  />
  <api:set attr="in.ElementMapName#" 					 value="totalRows" />


  <api:script method="GET">
    <api:set attr="in.URI" 			  	value="[in.BaseURI | replace('{pageNumber}', [in.startPage])]"/>
    <api:call op="jsonproviderGet" in="in" out="apiOut">
      <api:set attr="in.totalRows"      value= "[apiOut.totalRows]" />
      <api:set attr="in.endPage" 		value="[in.totalRows]"/>
	<api:push item="apiOut"/>
    </api:call>

    <api:if attr="in.endPage" value="[in.startPage]" operator="greaterthan">
      <api:set attr="in.continuePage" value="[in.startPage | add(1)]"/> 
      <api:enum range="[in.continuePage]..[in.endPage]">
        <api:set attr="in.Page" 			value="[_value]"/>
        <api:set attr="in.URI" 			  	value="[in.BaseURI | replace('{pageNumber}', [in.Page])]"/>
        <api:call op="jsonproviderGet" in="in" out="apiOut">
          <api:push item="apiOut"/>
        </api:call>	
      </api:enum>
    </api:if>
  </api:script>
</api:script>

 

Thomas Lind
Rank
Userlevel 6
Badge +5

You can find the JSONPath if you can get the actual JSON file and then view it in a text provider.

I like to use Postman to see this, but I assume there are other methods to see it.

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @aftabp did you manage to resolve the issue? If so please help us by marking a best answer above. Please let us know if you have further questions or updates

A
Rank

@Christian Hauggaard 
Hi,
Sorry for the late reponse. I was on vacation.
No, the issue still presists. 
I am still getting the error: 

[500] Could not execute the specified command: Formatter [ pages ] failed in the evaluation of [out.pages]. The error was:
The value of the attribute could not be accessed: The attribute does not exist.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @aftabp 

I was also out last week. Is it possible to send this file to me and am I able to connect to the source then?

You can send the data through a private message. You can attach a RSD file, if you rename the filetype to txt.

A
Rank

I have been working closely with the vendor today and we are still unable to solve the issue. According to the log, the data connection in TX reads all the records but doesnt put it in ODX.

Please see the attached RSD-file and the error log. The log files shows what comes up after the last from the provided API is read.

We have also tried reducing the number of records per page.

 

In the log, there is a statment taht says “Engine Invalid object name 'sys_resultset_close' “
I dont know what that is. Also, as suggested by som post i have also tried to increase the timeout from 15 to 300 but the issue is unsolved. 

 

 

2 Attachments
Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @aftabp 

I made a small change. I am not sure it is enough, but it did look like it may be the difference that made it unable to find the value.

1 Attachment
A
Rank

@Thomas Lind 
Unfortunately that didn’t solve the issue. I am still getting the same error:

[500] Could not execute the specified command: Formatter [ pages ] failed in the evaluation of [out.pages]. The error was:
The value of the attribute could not be accessed: The attribute does not exist.

Details:

Formatter [ pages ] failed in the evaluation of [out.pages]. The error was: ...
Module: System.Data.CData.REST
dwo220E.EgJ
   at dwo220E.ET.n()
   at dwo220E.EwK.a(RSBOperation , EIr )
   at dwo220E.EIr.D(Egc , EJS , OEz , EJS , EJS , String )
   at dwo220E.EIr.r(RSBOperation , EJS , OEz , EJS , EJS )
   at dwo220E.EuJ.Q(String , RSBOperation , RSBItem , RSBCallback )
   at dwo220E.EuJ.Call(RSBOperation , RSBItem , RSBCallback )
   at dwo220E.FrV.k(RSBContext , EcB`1 , Object , RSBItem , RSBCallback )
   at dwo220E.FrI.y(RSBContext )
   at dwo220E.FrI.Exec(RSBContext )
   at dwo220E.EwK.L(RSBOperation , EuJ )
   at dwo220E.EwK.a(RSBOperation , EIr )
   at dwo220E.EgB.Q(EgB , EIr )
   at dwo220E.EgB.se(EIr )
   at dwo220E.Ewh.v(EIr )
   at dwo220E.Ewh.o(Ewh , EIr )
   at dwo220E.Ewh.se(EIr )
   at dwo220E.Ewh.v(EIr )
   at dwo220E.Ewh.o(Ewh , EIr )
   at dwo220E.Ewh.se(EIr )
   at dwo220E.Ewh.se(EIr )
   at dwo220E.EJm.VI(EIr )
   at dwo220E.EwE.ff(EIr , Boolean )
   at dwo220E.EIr.D(Egc , EJS , OEz , EJS , EJS , String )
   at dwo220E.EIr.h(String , EJS , OEz , EJS , EJS )
   at dwo220E.EuJ.Call(String )
   at dwo220E.xJ.N(RSBContext , xI )
   at dwo220E.OVr.Yn()
   at dwo220E.OVw.Yn()
   at dwo220E.OEh.o(rg , yE )
   at dwo220E.OFO.xI()
   at dwo220E.OEh.fA()
   at dwo220E.xm.k(SQLQuery , Int32 , yL )
   at dwo220E.xm.x(IDataStatement , SQLQuery , QueryParameterCollection , Int32 , String , EnO )
   at dwo220E.OVd.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at dwo220E.dP.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at dwo220E.EKd.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at dwo220E.hw.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at dwo220E.BB.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at dwo220E.OSO.xY()
   at dwo220E.OSK.cu()
   at dwo220E.LS.cI(QueryParameterCollection )
   at System.Data.CData.REST.RESTCommand.ExecuteDataReader(CommandBehavior behavior)

[500] Could not execute the specified command: Formatter [ pages ] failed in the evaluation of [out.pages]. The error was: ...
Module: System.Data.CData.REST
System.Data.CData.REST.RESTException
   at System.Data.CData.REST.RESTCommand.ExecuteDataReader(CommandBehavior behavior)
   at System.Data.CData.REST.RESTCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at TimeXtender.DataManager.StepTransferSSISExecute.BulkCopySource(Boolean executeIncrementalLoad, DataSource dataSource, VariableResolveObject dynamicResolveObject)
   at TimeXtender.DataManager.StepTransferSSISExecute.DoStageDataSource(Boolean isIncrementalAllowed, DataSource dataSource, StepSetup stepSetup, VariableResolveObject dynamicResolveObject, Boolean loadPrimaryKeysOnTransfer)
   at TimeXtender.DataManager.StepTransferSSISExecute.DoStage(StepSetup stepSetup, VariableResolveObject dynamicResolveObject)

 

A
Rank

@Thomas Lind 
Not sure why but after i have commented out thie following, it worked:

<!-- <api:set attr="in.PageCount" value="[out.pages]"/> -->
<api:script method="GET">
		<api:set attr="in.URI" value="[in.BaseURI | replace('{page}', 1)]"/>
		<api:call op="jsonproviderGet" in="in" out="out">
			<!-- <api:set attr="in.PageCount" value="[out.pages]"/> -->
		</api:call> 

		<api:enum range="1..[in.PageCount]">
			<api:set attr="in.Page" value="[_value]"/>
			<api:set attr="in.URI" value="[in.BaseURI | replace('{page}', [in.Page])]"/>
			<api:call op="jsonproviderGet" in="in" out="out">
				<api:push item="out"/>
			</api:call>
		</api:enum>
	</api:script>
</api:script>

 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @aftabp 

It was one of my ideas for you to try. It makes sense that it will work, but now it only runs for the default pagecount amount that you have set.

Is it possible for you to send the whole log when the verbosity is set to 3 or higher? Of course while having the parts that give the error uncommented again.

A
Rank

The issue is now resolved. 
What caused the issue was that in API the meta was at the bottom and not on top of the body.

Instead of the image under where the meta is now in top (starting from line 3):

It was at bottom (starting from line 6404):

 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @aftabp 

What did you change to the file? The json path looks to be the same despite the order being different.

A
Rank

@Thomas Lind 
I asked the vendor to change the position for meta/pagination - move it from bottom to top.
Look at the images above and notice the position for the follwing code:

{
"code":200,
"meta":
{
"pagination":
{
"total":77282,
"pages":387,
"page":1,
"limit":200
}
}

The first screen grab (white background) is the one that made it work. You can see that code:200 is on line 2 instead of 6412 (second screen grab). Meta in first screen grab starts is on line 3 instead on line 6404.

 This is the only change the vendor did and the code worked. 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @aftabp 

Thanks for this, I guess the order is really important for this to work.

Reply


Terms and ConditionsCookie settings