Deadlock on logging variable value changes using a SQL task

Question

Morning

I've been reading "SQL Server 2008 Integration Services Problem - Design - Solution". It outlines a way of logging variable changes which I'm trying to replicate in SQL 2005.

1. Create variables e.g. PackageId, RecordsAffected. - Set Raise ChangeEvent to true.
2. Create a string variable g.g. strVariableValue. - Set Raise ChangeEvent to false.
3. On the package event handler: OnVariableValueChanged add a script task "SCR Convert value to string".
4. Add ReadOnlyVariables: System::VariableValue
5. Add ReadWriteVariables: User::strVariableValue
6. In the script, set a local variable to System::VariableValue.value.tostring
7. Set the variable User::strVariableValue to the local variable
8. Add an "Execute SQL Task" component "SQL Log Variable Value Changed" calling a SP with no resultsets.
9. Set parameter mapping to User::PackageId, System::VariableName, User::strVariableValue

When this is run, I get a deadlock on User::PackageID

Error: 0xC001405B at SQL Log Variable Value Changed: A deadlock was detected while trying to lock variable "User::_PackageID" for read access. A lock could not be acquired after 16 attempts and timed out.

The script step succeeds but the Execute SQL task fails. I'm using Visual Studio 2005 Version 8.0.50727.42, Microsoft SQL Server Integration Services Designer Version 9.00.4035.00 and BIDSHelper Version 1.4.3.0.

Any ideas?

Answer 1

Did you use the code sample from the book? All the files are available on the Wiley website for free. The code sample includes a SSIS package, sql scripts, and VB code for the script. If this doesn't work for you, then let me know since one of my team members found a way to log variable changes that was different from this methodology.