Data Management

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.

6 comments
nmcdermaid
nmcdermaid

There's bigger problems than this. I've found with more complex integrations that you still end up loading the data into a staging table and using an SP to process it internally. Thats what annoyed me about DTS, it ended up just being a framework for loading stuff into staging tables then running the appropriate SP to process it. Although IS is much more advanced and you don't need to do crazy ActiveX hacks for looping through files etc., there are sill situations where its easier in T-SQL to do some stuff. Oh well can't have everything, and it is definitely a vast improvement over DTS.

Vette95
Vette95

I think the terminology may be confusing here. The option selected in the drop down is "SQL Command" and there does not appear to be an option for running a "SQL Script" which would be more appropriate for multiple statements; for instance, the use of SET ROWCOUNT on the line before the SELECT statement. To get what you want out of this situation, would be to use the "top x" in your select statement. In the documentation it is listed as an alternative to set rowcount. Plus, there is no concern to reset the ROWCOUNT option to prevent limiting results of subsequent queries. Good luck!

RexWorld
RexWorld

You're right but then why does the Preview button on that dialog return a result when using set rowcount? The Preview button should exhibit the same behavior as the actual project when it executes. Otherwise the Preview button is very misleading, since it does in fact returns results when using set rowcount.

Justin James
Justin James

You know, I was thinking the same, but I don't have much experience with SSIS... that makes perfect sense to me too! J.Ja

Vette95
Vette95

I tested it with both SET ROWCOUNT 20 and using a SELECT TOP 20. I also tested the Preview option without a limiting factor, just to be sure and it will always show just the first 200 records (in our example, our table only has 115) as it states at the top of the preview window. The first time around it did not produce output, just as you indicated, however, it was due to a tranformation error on the XML column. I removed the column from the selection query and the output result set (I tested with a flat file output, then with an error set going back into the database in a new table). The package worked creating my flat file and with either SET ROWCOUNT 20 or using the TOP 20 clause in the select statement. You may want to take a look at the dataflow window when you execute the package for any red colored objects. A successfull run will show all objects in a green color. Also check the Progress tab when you test and examine the lines for any errors that were logged, they can sometimes be more descriptive of issues than the standard output window at the bottom of Visual Studio. Again, Good Luck.

Justin James
Justin James

The problem is that the Preview component is the same preview component used everywhere else, which runs a script. That's the real bug, it is misleading. I spoke to a fellow yesterday who used to be a developer on the SQL Server project, he chuckled about this because it is not uncommon for folks to get hung up on this. It boils down to a matter of having 1 "gotcha" in the system, or writing a second "Preview" component just for this one part of the program. J.Ja