Lookup concurrency issue in packages running simultaneously in parallel

I have a system of SSIS packages in which several packages perform the same lookup on the same table. E.g., i have PackageA, PackageB and PackageC all doing a lookup on TableA. All of these packages are spawned by the same PackageD and run frequently. In some cases, there is an issue with concurrency on these lookups. I get the following exception :

"

The ProcessInput method on component "LKP Lookup SecurityID" (6658) failed with error code 0xC004702C. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

"

The hex code of this exception corresponds to the following description : "DTS_E_BUFFERNOTLOCKED. This buffer is not locked and cannot be manipulated." That's as much as i could find on this.

My suspision is that the SSIS engine somehow figures that the lookup in these distinct packages is the same one and builds a shared version of the lookup table in memory. Then there is some sort of a multi-threading issue in accessing this shared memory which leads to the exception above.

Has anyone experienced this? Can someone shed some light on this?

Thanks a lot

-Alex

Just out of curiosity, did you copy-n-paste the lookups across the packages?

Are you using SQL in the lookup (you should be!) or are you simply choosing the table?|||

Phil,

i used a template package that had the lookup defined. I generated a new package ID for each of the derived packages.

i am using the default setup for the lookup, without messing with SQL. The table is very small (<300 rows) and is loaded into memory entirely. What advantage would i get from modifying SQL? Thanks

|||

chianuri wrote:

Phil,

i used a template package that had the lookup defined. I generated a new package ID for each of the derived packages.

i am using the default setup for the lookup, without messing with SQL. The table is very small (<300 rows) and is loaded into memory entirely. What advantage would i get from modifying SQL? Thanks

Well, I'm just curious to know if you keep things set up the way you do, but RECREATE each lookup (sounds like it would be a quick task) in each package, what happens then?

As far as using SQL, it is always preferred to use SQL in any component, if possible. Numerous reasons, but the two primary ones are so that you can select just the columns you need, and can filter the rows ahead of time using the power of the SQL Server engine versus SSIS.

For your issue, I was just curious to know what happens if you replace the table selection with SQL. If the same thing happens, then what happens if you alias the lookup table differently in each lookup. (select ... from table a ; select ... from table b ; etc...)|||

chianuri wrote:

Phil,

i used a template package that had the lookup defined. I generated a new package ID for each of the derived packages.

The fact that you are using a template and your data-flow exists in that template makes me *very* suspicious. Perhaps there is a handle that is replicated across those multiple packages. It could be that you have stumbled upon a bug.

Would it be possible for you to post a repro package? Perhaps one that works (i.e. fails) when run against AdventureWorks?

Thanks

Jamie

|||

Phil Brammer wrote:

As far as using SQL, it is always preferred to use SQL in any component, if possible. Numerous reasons, but the two primary ones are so that you can select just the columns you need, and can filter the rows ahead of time using the power of the SQL Server engine versus SSIS.

Phil is absolutely correct. Don't assume that selecting from the dropdown is the same as issuing a "SELECT * FROM...". As explained here:

SELECT *... or select from a dropdown in an OLE DB Source component?

http://blogs.conchango.com/jamiethomson/archive/2006/02/21/SSIS_3A00_-SELECT-_2A002E002E002E00_-or-select-from-a-dropdown-in-an-OLE-DB-Source-component_3F00_.aspx

-Jamie

|||

Phil Brammer wrote:

Well, I'm just curious to know if you keep things set up the way you do, but RECREATE each lookup (sounds like it would be a quick task) in each package, what happens then?

Actually, it's not going to be that easy in my case as i have a fairly large number of packages in total ( >30), and many of them share lookups. I just gave an oversimplified account of the system in my original description of the problem.

Phil Brammer wrote:

As far as using SQL, it is always preferred to use SQL in any component, if possible. Numerous reasons, but the two primary ones are so that you can select just the columns you need, and can filter the rows ahead of time using the power of the SQL Server engine versus SSIS.

For your issue, I was just curious to know what happens if you replace the table selection with SQL. If the same thing happens, then what happens if you alias the lookup table differently in each lookup. (select ... from table a ; select ... from table b ; etc...)

This suggestion seems to be more manageable in my case (as i won't need to recertify all the metadata downstream from the lookup once i replace it). I'll keep you posted on what happens. I'm still curious as to what exactly causes the error in the first place.

I'm wondering if SP2 fixes this problem since it does fix another issue with the lookup component...

I'd appreciate if anyone else who has experienced anything of this sort or has more details on 0xC004702C (DTS_E_BUFFERNOTLOCKED) responded to this post.

|||

Jamie Thomson wrote:

chianuri wrote:

Phil,

i used a template package that had the lookup defined. I generated a new package ID for each of the derived packages.

The fact that you are using a template and your data-flow exists in that template makes me *very* suspicious. Perhaps there is a handle that is replicated across those multiple packages. It could be that you have stumbled upon a bug.

Would it be possible for you to post a repro package? Perhaps one that works (i.e. fails) when run against AdventureWorks?

Thanks

Jamie

Thanks for the reply Jamie. I'll put something together and post it, but it is difficult to replicate this bug since it happens non-deterministically (at least it looks that way on the surface).

|||

chianuri wrote:

Thanks for the reply Jamie. I'll put something together and post it, but it is difficult to replicate this bug since it happens non-deterministically (at least it looks that way on the surface).

I'll see if I can recreate it as well.

If you need somewhere to post your package, you can contact me or very likely Jamie as well. (Don't want to volunteer him outright! )

I know we are all interested to know if this is a bug and if so, is there a workaround.

Thanks,
Phil|||

Phil Brammer wrote:

chianuri wrote:

Thanks for the reply Jamie. I'll put something together and post it, but it is difficult to replicate this bug since it happens non-deterministically (at least it looks that way on the surface).

I'll see if I can recreate it as well.

If you need somewhere to post your package, you can contact me or very likely Jamie as well. (Don't want to volunteer him outright! )

I know we are all interested to know if this is a bug and if so, is there a workaround.

Thanks,
Phil

Thanks for your replies, Phil.

I started converting my lookups to SELECTs, as both you and Jamie suggested and will post the results here.

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

|||

chianuri wrote:

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

And I've raised this issue before, but was told that even though the tasks/components have the same GUID, the package's GUID is different and hence should ensure uniqueness. I've always been skeptical of that claim. So pretty much since then, I have not done much copy-n-pasting.|||

Phil Brammer wrote:

chianuri wrote:

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

And I've raised this issue before, but was told that even though the tasks/components have the same GUID, the package's GUID is different and hence should ensure uniqueness. I've always been skeptical of that claim. So pretty much since then, I have not done much copy-n-pasting.

Yes, this is exactly what i'm talking about! I knew someone must've experienced this problem before....

Would you happen to have an update on that request from the SSIS team? Are they planning for a hotfix anytime soon?

|||

chianuri wrote:

Phil Brammer wrote:

chianuri wrote:

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

And I've raised this issue before, but was told that even though the tasks/components have the same GUID, the package's GUID is different and hence should ensure uniqueness. I've always been skeptical of that claim. So pretty much since then, I have not done much copy-n-pasting.

Yes, this is exactly what i'm talking about! I knew someone must've experienced this problem before....

Would you happen to have an update on that request from the SSIS team? Are they planning for a hotfix anytime soon?

No. No update because they don't feel there's an issue, until now, perhaps. You should visit my Connect submission and post your feedback, with detailed notes.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=241901|||

Phil Brammer wrote:

chianuri wrote:

Phil Brammer wrote:

chianuri wrote:

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

And I've raised this issue before, but was told that even though the tasks/components have the same GUID, the package's GUID is different and hence should ensure uniqueness. I've always been skeptical of that claim. So pretty much since then, I have not done much copy-n-pasting.

Yes, this is exactly what i'm talking about! I knew someone must've experienced this problem before....

Would you happen to have an update on that request from the SSIS team? Are they planning for a hotfix anytime soon?

No. No update because they don't feel there's an issue, until now, perhaps. You should visit my Connect submission and post your feedback, with detailed notes.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=241901

done... hope they respond to it

|||

I hope they respond. This definately looks like a bug.

I've never liked the fact that they don't generate new IDs for all the package objects even though I didn't have a real good reason for it. it just didn't "smell" very good. How hard is it to loop over the executables collection and components collections and change all the IDs?

Good spot guys!! Although shame we can't get a repro.

-Jamie