Error when scheduling through SQL Server Agent

Topics: Issues 3: Executing the Component (Run-Time)
Apr 7, 2010 at 10:15 AM

Hi,

I have successfully implemented the Kimball SCD Component in my project and works fine and fast. When I execute the package in the SSIS designer, everything works well. However, when I schedule the package in an SQL Server Agent job, the job fails with the following error:

Started:  8:05:21  Error: 2010-04-07 08:06:39.29     Code: 0x00000000     Source: Update data SCD Type 2 Kimball Method Slowly Changing Dimension     Description: Internal error (Value does not fall within the expected range.) in PrimeOutput retrieving output buffer column indexes for output 'Unchanged', column 'AccountKey'.  End Error  Error: 2010-04-07 08:21:16.84     Code: 0x00000000     Source: Update data SCD Type 2 Kimball Method Slowly Changing Dimension     Description: Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 310065006500620061003500650035002d0061006300380034002d0034003300320037002d0061003600620037002d00300030003500350035003500620065003000340035003300.  End Error  Error: 2010-04-07 08:21:16.84     Code: 0x00000000     Source: Update data SCD Type 2 Kimball Method Slowly Changing Dimension     Description: Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 620066003600340039003000370063002d0039006300660034002d0034003700360063002d0061006500650066002d00300030003100660030003900630037006600320030003800.  End Error

This error is repeated about five times, even with the same of those very long ID strings like above. I have no idea where to look for what I have done wrong, so hopefully someone can point me in the right direction!

Thanks!

Coordinator
Apr 7, 2010 at 8:47 PM

Let me translate the first error for you best I can: the component is attempting to find the column "AccountKey" in some internal SSIS structures for the "Unchanged" output... but it's not there.  Needless to say - it should be :)  The subsequent errors are caused by the first - actually the component shouldn't let the execution get that far, it should fail the flow right away.  (Just changed that.)

What version of the component are you using?  When you execute by Job, is the server on the same computer you've successfully run it on in BIDS?  Or is the job on a server?  Have you installed the same version of KSCD on that server?

Apr 15, 2010 at 4:04 PM

I am getting the same thing. It works great by itself but as soon as i call the packge from a Execute Package task then i get the error below. I have the "Unchanged" output going to a derived Column transformation. It is all on the same computer.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d002000340030003200300032003500.
Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d002000340030003200300032003500.) in ProcessKey, case 6 - Unchanged, key: 32003000340020002d002000340030003200300032003500.

Kimball Method SCD v1.5

KimballMethodSCD90.dll

Coordinator
Apr 15, 2010 at 11:38 PM

Doesn't look like you're getting the same thing - erikm was getting one more error message.  Are those two error messages the only errors you see?

Apr 16, 2010 at 12:45 AM

Here is the full Error

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d002000340030003100360031003100.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d00200034003000390030003800.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d002000340030003200300032003500.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d00200034003000390030003800.) in ProcessKey, case 6 - Unchanged, key: 32003000340020002d00200034003000390030003800.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d002000340030003200300032003500.) in ProcessKey, case 6 - Unchanged, key: 32003000340020002d002000340030003200300032003500.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d002000340030003100360031003100.) in ProcessKey, case 6 - Unchanged, key: 32003000340020002d002000340030003100360031003100.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d002000330039003000360038003800.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Internal error (Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d00200034003000390030003800.) in ProcessKey, case 6 - Unchanged, key: 32003000340020002d00200034003000390030003800.) in ProcessKey detecting changes, key: 32003000340020002d00200034003000390030003800.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Internal error (Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d002000340030003200300032003500.) in ProcessKey, case 6 - Unchanged, key: 32003000340020002d002000340030003200300032003500.) in ProcessKey detecting changes, key: 32003000340020002d002000340030003200300032003500.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d002000330039003000360038003800.) in ProcessKey, case 6 - Unchanged, key: 32003000340020002d002000330039003000360038003800.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Internal error (Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d002000340030003100360031003100.) in ProcessKey, case 6 - Unchanged, key: 32003000340020002d002000340030003100360031003100.) in ProcessKey detecting changes, key: 32003000340020002d002000340030003100360031003100.

Error: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Internal error (Internal error (Internal error (Object reference not set to an instance of an object.) ProcessCache_SendExistingToUnchanged key: 32003000340020002d002000330039003000360038003800.) in ProcessKey, case 6 - Unchanged, key: 32003000340020002d002000330039003000360038003800.) in ProcessKey detecting changes, key: 32003000340020002d002000330039003000360038003800.

SSIS package "JS Build - Dimension.dtsx" finished: Canceled.

 

Apr 21, 2010 at 3:02 PM

Does anyone have any idea how to fix this or some work around? I really want to use this component because standalone it works great but it is worthless to me if i cannot schedule the job.

Coordinator
Apr 30, 2010 at 6:56 AM

What version are you using?  And what's the date on your KimballSCD assembly?

Apr 30, 2010 at 6:04 PM

See attached



From: [email removed]
To: [email removed]
Date: Thu, 29 Apr 2010 22:56:30 -0700
Subject: Re: Error when scheduling through SQL Server Agent [kimballscd:208541]

From: toddmcdermid
What version are you using? And what's the date on your KimballSCD assembly?
Read the full discussion online.
To add a post to this discussion, reply to this email (kimballscd@discussions.codeplex.com)
To start a new discussion for this project, email kimballscd@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Got a phone? Get Hotmail & Messenger for mobile!
May 7, 2010 at 1:25 PM

I am also using v1.5, and the unchanged output just goes to a derived column. So when I run it in BIDS, everything works perfectly fine, but as soon as I schedule it through an SQL Server Job, I get these errors.

 

Oct 1, 2010 at 12:23 PM

Hi Todd,

I'd like to bump this post again, since I haven't been able to figure out why this error occurs. It's no option for me to use the standard SCD component because that thing is sooo slow! Your solution is incredibly valuable for me to use, but if I can't schedule it, there's not much point for me to use it.

Thanks!

Erik

Oct 15, 2010 at 6:55 PM

I have a fix!! I too have converted from using the stock SCD to Todd's fantastic KSCD, strickly for the performance improvements.  I have also ran into the problem mentioned in this thread, and I have found a work around that works just fine for me.  I had the UNCHANGED rows going to a derived column item with no further flow from there.  I thought to myself, what is the point of having them go anywhere except when needing to debug?  So to get rid of this path, I went into the Kimball SCD, selected the Output Column Selection tab, selected the Unchanged output and unselected all boxes in the Include column.  My package with the Kimball SCD now runs just fine in a SQL job.  Hope this helps.

Jeff.

Coordinator
Oct 21, 2010 at 1:13 AM

Ah ha...

That makes (some) sense that I'll have to dig into.

Here's what's probably going on.  Since you have those columns on the Unchanged output marked as "output this" in my Output tab, I'm attempting to dump values out to those columns.  Problem is - they might not be there!  Why?  Because SSIS may decide that they're not necessary!  It's a great optimization feature built-in to SSIS.  If those columns aren't used later on in the data flow - basically all the warnings you get about "unused columns" - then SSIS may decide to simply eliminate them from the data flow to save memory that isn't really being used.  I'm probably (in v1.5) being dumb about expecting them to be there anyway.  I believe I've rectified that in v1.6... but I'll check.

Oct 21, 2010 at 10:41 AM
Edited Oct 21, 2010 at 10:42 AM

Jeff, that solved it! Thanks a lot!!  Todd, if there is a way to fix it, that would be helpful, because maybe in the future it would be nice to have a further flow with the unchanged records.

Coordinator
Oct 22, 2010 at 8:28 PM

It should be fixed in v1.6.  Sounds like you're finding the component useful.  Be sure to sign up to notifications or the RSS feed for the v1.6 status discussion.