mySQL LIMIT Exception

[adameverington]

Hi guys,

I'm in need of batching a mySQL query. I'm wanting to do this by dynamically setting the mySQL Source Query as such:

Code: Select all

SELECT AccountNumber,SupplierInvoiceNumber
FROM tblInvoice 
GROUP BY AccountNumber,SupplierInvoiceNumber
ORDER BY AccountNumber,SupplierInvoiceNumber
LIMIT '<@User::recordsAffected>','<@User::batchSize>'

...but can't get it to work because of a String.Format error. Additionally even if I hardcoded the limit values in I still can't execute it and the error message is incorrect. It displays:

"near limit 0,100"

regardless of the batch and skip sizes I put in.

Anyone help?

[adameverington]

So a little further on...

by adding a semi-colon to the end I can see it executing on the server but the "take/batch size" is ALWAYS 100 regardless of what I put in. Is this hard-coded in the component?!

[Pinturiccio]

We have reproduced the issue. The reason of the issue is that preview shows first 100 records by default. For this, we add the "LIMIT 0,100" clause to the end of the query. When a query contains two LIMIT clauses, an error is generated. This issue is reproduced only when previewing data in a Source component. When running such package, the "LIMIT 0,100" clause is not added to the query, and it is processed correctly. You can see it by adding a Data Viewer to your package.

We will post here when the issue is fixed.

[adameverington]

Fantastic. Could you also confirm the error when trying to add the LIMIT in dynamically?

[Pinturiccio]

Please describe in more details what you mean by "add the LIMIT in dynamically". If you mean using variables in a query, then yes, the reason of the issue is the same.

[adameverington]

Exactly that

[Pinturiccio]

Could you also confirm the error when trying to add the LIMIT in dynamically?

We have fixed the bug when a LIMIT clause is added to a Source component query. We will post here when the corresponding build of SSIS Data Flow Components is available for download.

[Pinturiccio]

New build of Devart SSIS Data Flow Components 1.6.643 is available for download now!
It can be downloaded from https://www.devart.com/ssis/download.html or from Customer Portal (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=36605.