Issues when reading data from SAP into SQL using .NET Framework Data Provider for mySAP Business Suite

Question

When I try to Import data into SQL using SSIS package, the query used for SAP is

SELECT AFVC.MANDT, AFVC.AUFPL, AFVC.APLZL, AFVC.PROJN, AFVC.LOEKZ

FROM AFVU INNER JOIN AFVC on AFVU.AUFPL=AFVC.AUFPL and AFVU.APLZL = AFVC.APLZL

WHERE AFVC.LOEKZ != 'X' and AFVU.USR00 LIKE 'CritPath%'

OPTION 'DISABLEDATAVALIDATION, NO_CONVERSION'

This always returns a limited dataset for 10000 records. But If I add a BatchSize option for eg:

OPTION 'DISABLEDATAVALIDATION, BATCHSIZE=20000;NO_CONVERSION' It will return 20000 records.

I want to understand is this a limitation. If there us no joins in the query it will return the entire data from table AFVC

your help is appreciated.

Answer 1

Hi @Mithal Chodankar,

As said in Syntax for a SELECT Statement in SAP.

When set to batchsize <size>, the execution of the SELECT statement causes multiple calls to be made to the SAP system, and in each call, only <size> number of records are retrieved.

For example, if you specify 'batchsize 100', the SELECT query retrieves 100 records only in each call to the SAP system.

If batchsize <size> is not specified, the default value of 10,000 is assumed for the batch size. Note that you should specify an optimum value for the batch size based on the physical memory of the computer and the number of rows in the SAP system. Failure in specifying an optimum value for batch size may result in out of memory exceptions.

You may have a double check.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.