Page 1 of 1
mySQL LIMIT Exception
Posted: Wed 17 Jan 2018 11:18
by 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?
Re: mySQL LIMIT Exception
Posted: Wed 17 Jan 2018 11:27
by 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?!
Re: mySQL LIMIT Exception
Posted: Fri 19 Jan 2018 13:43
by 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.
Re: mySQL LIMIT Exception
Posted: Mon 22 Jan 2018 08:50
by adameverington
Fantastic. Could you also confirm the error when trying to add the LIMIT in dynamically?
Re: mySQL LIMIT Exception
Posted: Wed 24 Jan 2018 13:53
by 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.
Re: mySQL LIMIT Exception
Posted: Thu 01 Feb 2018 13:43
by adameverington
Exactly that
Re: mySQL LIMIT Exception
Posted: Wed 14 Feb 2018 15:06
by Pinturiccio
adameverington wrote: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.
Re: mySQL LIMIT Exception
Posted: Thu 15 Feb 2018 15:44
by 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.