Developing Matt

My Technical Journal

My SSIS Package is Missing Rows Randomly

leave a comment »

Does your ssis package ever miss data?  It seems like sometimes it just misses pieces of information here and there…randomly. 

I had this nagging problem:  I would run my package and all of my data would update/insert fine except for one or two pieces.  I would then dig through the code, procedures, etc…not find anything and finally run the package again on the file and it would work.  All data would update/insert correctly. 

I refused to

a.) believe that SSIS wasn’t processing everything
b.) build a job to run the file twice.

More specifically, my lookup didn’t seem to find stuff occasionally.  Have you ever noticed that when you run your package some objects light up yellow before it finishes the previous operation? 

untitled

This clued me in to the possibility that something was being prepared before I was ready for it to be prepared.   It’s not in the picture, but  my lookup was occurring at the same time as my insert so it was missing the new data because it was caching the data before I was ready for it to be cached.

So after reviewing this page I changed my lookups to ‘No Cache’ and never had the problem again.  Setting it to no cache made it go back to the server to retrieve the data which enabled it to catch the new inserts.  Because I was working with a small amount of data I wasn’t concerned with memory issues.

20100415131042_img_8797b

Advertisements

Written by matt

April 30, 2010 at 2:29 pm

Posted in SSIS

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

%d bloggers like this: