The following error is a new thorn in my side. Itâ€™s showing up in SSIS 2005 packages which are scheduled, but does not happen when I run the packages manually. I changed the message slightly to remove identifying characteristics.
Executed as user: <MachineName>\SYSTEM. …n 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:00:00 AM Error: 2008-06-17 04:01:28.67 Code: 0xC001405D Source: Script Task Description: A deadlock was detected while trying to lock variables "<comma delimited list>" for read access and variables "<comma delimited list>" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out. End Error Error: 2008-06-17 04:01:28.67 Code: 0x00000005 Source: Script Task Description: The script files failed to load. End Error
The SQL DTS Wiki has some talk of the issue:
This error happens when two processes are trying to get a lock on the same variable. It is not a deadlock in the same way as a SQL Server, to use that terminology it is just a blocking scenario. When blocked, the process will have 16 attempts and then fail with this error. Certain problems exacerbate this issue, the obvious being time the locks are held, and write locks over read locks. Several issues have been addressed with the post-SP1 update, KB918222 Cumulative hotfix package (build 2153) for SQL Server 2005. System variables are now only read locked, which makes sense as they are always read-only anyway. If you encounter this problem, try SP1 + KB918222 to start with, then try and look at your package design and minimise locks taken, and minimise parallel processes that use the same variables
(full entry at http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC001404D.html)
The recommendation to install SP1 + hotfix rollup is an issue, since Iâ€™ve patched the machine to SP2 already, and am seeing the error. A contributor to the page offered the following suggestion on her blog:
I eventually realised that the only other place I had added the variable was to to the ReadWriteVariables list in the properties of the Script Component:
As soon as I removed this variable name from that field, I was able to get my script component to successfully run.
There is a script task with ReadWriteVariables, but removing them threw a different error, since the script needs access to the variable.
After picking through the post-SP1 hotfixes, KB918091 seems likely to be the one which resolves this issue. The error messages are different, but the symptoms are similar:
If you try to run a Microsoft SQL Server 2005 Integration Services (SSIS) package, you may receive an error message that resembles one of the following error messages:
Error message 1
The variable "User::MyVariable" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Error message 2
Reading the variable "User::MyVariable" failed with error code 0xC0010009. The expression "@[User::MyVariable]+"\\"+ @[User::MyType]" on property "ServerName" cannot be evaluated. Modify the expression to be valid.
The error messages may be reported in several locations:
â€¢ When you try to run an SSIS package by using the dtexec command-prompt utility, the error message may be reported in the console window of the command line.
â€¢ When you try to run an SSIS package by using SQL Server Business Intelligence Development Studio, the error message may be reported in the Business Intelligence Development Studio output window.
â€¢ When you try to run an SSIS package and you use a log provider to redirect the execution output, the error message is reported in the redirected output.
The list of bugs fixed in SQL Server 2005 SP2 does not include this hotfix, so itâ€™s the next step.
Microsoft has a new process for delivering hotfixes. After completing the process and downloading the file, the following error was thrown when I executed the downloaded file:
â€œThis self-extracting zip file is part of a multidisk zip file. Pleas insert the last disk of the set.â€
I replied to the e-mail with the download link, but the reply bounced back.
After spending 90 minutes on the phone with Microsoft tech support (thanks Anu for the help!), we determined the original download was corrupted. They sent me a new link, and I redownloaded the hotfix. The install started correctly, but the hotfix threw a new error:
06/19/2008 11:58:30.235 ================================================================================
06/19/2008 11:58:30.235 Hotfix package launched
06/19/2008 11:58:32.985 Version level check successful for product instance MSSQLSERVER
06/19/2008 11:58:33.001 This update requires service pack 0. The service pack for product instance MSSQLSERVER is 2. Download the update for service pack 2.
Some more research led us to KB 918222, which does list this bug as being fixed (note that the SP2 list uses KB article, while this list uses bug report numbers). Take home message from my 90 minutes of MS help is:
Take it for granted if your hotfix is in a previous compilation, itâ€™s in the SP, even if itâ€™s not specifically listed in the Kb article for that SP. When an SP is rolled out, it includes all hotfixes in the previous SPs and cumulative rollouts. The KB article for an SP lists only the hotfixes not included in any of the previous compilations.
The next step is to submit another bug report for my error and do some more debugging with MS.
Continuing story, watch this post for edits.