public class ben:harrell

July 10, 2007

SSIS Row Limits and DefaultBufferMaxRows

Filed under: C#, database, errors, Integration Servicees, SQL Server 2005, SSIS, Technology — benjamin harrell @ 9:51 pm

I had an odd issue during some testing I was doing on a custom source component I’m writing for EBCDIC data conversion.  I first assumed that surely my code was at fault but the test just seemed to simple to be “my code”.  So here’s the scenario,  I have a file with 100,000 bytes of the number ‘1’ in EBCDIC.  I created a simple package to import the file as 1 byte rows into a Dataset Destination.  I was surprised when I executed the package and found that only 65,536 rows were processed.  Now if you have been around software for more than a week you probably recognize this magic number and unfortunately it triggers all sorts of theories about what could possibly be going wrong.  Well, I’m still trying to investigate the “why” and I’ll update this post and welcome comments if anyone has input but for now the solution to this oddity is to increase the DefaultBufferMaxRows to greater than your number of records.  This seems like a horrible workaround for a situation that should happen quite often so the hunt continues…

Advertisements

3 Comments »

  1. Same problem here,

    created query with sqlplus that’s generates 344743 records. When executing the same query within SSIS only 60000 records are imported. I’ve still not found the problem. I’m quering on a oracle 9 database with a oracle 9i client(+ latest patch). Also I’ve installed the latest SP for visual studio en SQL 2005.

    Currently i’m trying out the max buffer sizes that causes some fluctuation in the amount of imported records.

    Help is needed.

    Thankz,

    Ron Killaars
    IT Engineer

    Comment by Killaars — August 13, 2007 @ 3:07 am

  2. did you make sure you were calling SetEndOfRowSet on all buffers?

    Comment by benjamin harrell — August 23, 2007 @ 11:39 am

  3. Hi, i believe that i noticed you visited
    my web site so i came to go back the choose?.I am attempting to to find issues to enhance my
    site!I assume its good enough to use some of your ideas!!

    Comment by Breanna — January 18, 2014 @ 7:46 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: