SSIS has a strange little bug with set rowcount

SQL Server 2005 Integration Services (SSIS) has a strange little bug that ignores "set rowcount" but accepts "top xx" syntax. It's a little annoying, but if you know it's there, you can work around it without too much effort.

Last week, I described how impressed I was with my first experience using SQL Server 2005 Integration Services (SSIS). Well, I've run into a quirk with the way SSIS handles certain SQL constructs. It doesn't make me change my overall impression of SSIS, but it's enough to take the bloom off the rose.

The issue is with the OLE DB Source (which is one of the items in the Data Flow toolbox). Double-click the icon to open the OLE DB Source Editor:

OLE DB Source

In the Data Access Mode drop-down field, I selected SQL command instead of the default table or view. This opens up the SQL editor so you can enter pretty much any SQL you want. This is where the bug (or quirk) shows up.

Say you want to limit the number of rows that come back from a query. Assuming your data source is SQL Server (which it is for my example), there are at least two ways I know to do that. The first would be to set a rowcount:

set rowcount 20
select * from DatabaseLog

Alternately, you can use the top xx syntax, like this:

select top 20 * from DatabaseLog

If you put either syntax into the above SQL editor box and hit the Preview button, you get identical results. You might think everything is fine; however, when you go try to run your SSIS project, if you're using the set rowcount syntax, you get zero rows back. Again, this is despite the fact that the Preview button on the OLE DB Source Editor returned the same results for both SQL statements.

This is annoying. If SSIS is not going to accept the set rowcount syntax, it should be consistent; it should return the same empty result when executing and when using the Preview button.

I'm still fairly impressed with SSIS, but this issue makes me wonder what other strange bugs are lurking in there. My SSIS honeymoon is officially over.