I have developed a package using SSIS 2005 that will connect to a SQL Server instance and perform server maintenance. Since I want this package to run on multiple servers, and be able to easially update it for all servers at once, I have the package sitting on a Network Share that all of the SQL Agent Accounts have Read/Write Access to. I have recently added 4 SQL 2008 Instances that I wanted to use the same maintenance package for, so I went ahead and created a new Job in the SQL Agent that calls the existing package. On 3 of the 4 instances the package runs successfully every time. On the last 2008 Instance the package fails most of the time with the error message output in the log below. I know the package works, because it will sometimes work as expected. Since the Error message says the application was busy and to Retry, I attempted to stagger the time and make sure this was the only insance running at the time, but it still fails more often than it succeeds. The Script Task that is referenced below accepts 3 string variables from the package and builds a SQL Statement that is returned to another variable for use further down in the package. Any ideas what I can look at to fix this? Another thought I had was to open the package in SSIS 2008 to do a single update, but I'd prefer to maintain a single package if possible since it still runs on SSIS 2005 instances as well. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:41:22 PM Error: 2010-11-29 12:41:53.11 Code: 0x00000003 Source: BuildSelect Database Query BuildSelect Database Query Description: There was an exception while loading Script Task from XML: System.Runtime.InteropServices.COMException (0x8001010A): The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER)) at EnvDTE80.Solution2.get_FileName() at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.InitNewScript(String languageID, String projectname, String projectext, Boolean bCleanupOnClose) at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.InitNewScript(String languageID, String projectname, String projectext) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.MigrateVSAScriptTask(XmlElement elemProj, IDTSInfoEvents events) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events) End Error Error: 2010-11-29 12:42:23.31 Code: 0x00000003 Source: BuildSelect Database Query Description: The Script Task is corrupted. End Error Error: 2010-11-29 12:42:23.31 Code: 0xC0024107 Source: BuildSelect Database Query Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:41:22 PM Finished: 12:42:23 PM Elapsed: 60.812 seconds--wayne

What is in the script? There must be something different about that SQL Server 2008 instance I'd say to open the discussion.Arthur My Blog

It's just a few If..Then statements with basic string concatenation. I am dynamically building a SQL Select statement based on the variables passed in. The generated SQL Statement is then used further down in a separate Execute SQL Task. I don't think it has anything to do with the code itself because it is failing during validation and thinks the task is corrupted. It doesn't appear to ever execute it.--wayne

I think it does, most probably it tried to raise an error message (window). I suggest you re-examine the code + try to run it interactively against this server and you will see it.Arthur My Blog

OK, I've pasted the code below. I've gone through it again today, and I don't see anywere that an interactive prompt would pop up. It really just does string concatination. For the server in question, it does complete successfully sometimes, so I know the variables are successfully getting populated. I have checked to make sure the variables don't change while running this multiple times in succession. Running it with no setup changes, could produce 1 successful run with 4 unsuccessful runs. The code should generate the same thing each time. Public Sub Main() Dim IncludeAllDBs As Boolean = DirectCast(Dts.Variables("IncludeAllDBs").Value, Boolean) Dim DBPriorities As String = Dts.Variables("DBPriorities").Value.ToString Dim DBExclusions As String = Dts.Variables("DBExclusions").Value.ToString Dim dbSQL As String If IncludeAllDBs Or Not String.IsNullOrEmpty(DBPriorities) Then dbSQL = "SELECT [name] FROM sys.databases WHERE " If IncludeAllDBs Then dbSQL &= "',tempdb,model" If Not String.IsNullOrEmpty(DBExclusions) Then dbSQL &= "," & DBExclusions End If dbSQL &= ",' NOT LIKE '%,' + [name] + ',%' " Else dbSQL &= "'," & DBPriorities & ",' LIKE '%,' + [name] + ',%' " End If dbSQL &= "AND state_desc = 'ONLINE' AND [compatibility_level]>=90" Dts.Variables("DatabaseQuery").Value = dbSQL Dts.TaskResult = Dts.Results.Success Else Dts.TaskResult = Dts.Results.Failure End If End Sub --wayne

So can you run it interactively until you get the error? You may drop this script task and recreate it, redeploy the package. Re-try.Arthur My Blog

I assume you mean using DTExecUI for running interactivly? The file has been replaced the file a few times over the last couple weeks as I have made changes to the process.--wayne

Yes, or just double-click/right click, choose execute it.Arthur My Blog

I've run it manually a few times now, and it's doing fine... (Of course manually it's running under my credentials which have slightly different permissions on all of these servers, don't know if that will play into it...) Depending on how things go from the SQL Agent tonight, I'll play with it again tomorrow.--wayne

You may want to enable package logging for this package, and then use Dts.Events.FireInformation method calls to log information from inside the Script itself. The original error message you posted indicates to me that it is not actually executing the script at all. The error message indicates that the SSIS engine is attempting to use the VSTA components to run the script, but is failing because that subsystem is busy. You've indicated that you've tried to ensure that this package is the only one running at that time. Are there any other applications being executed on that server at the same time? Applications that use COM, perhaps? Talk to me now on

Just to mess with me, the Agent ran the job perfectly last night, but it failed again after kicking it off this morning to test it. Todd, I agree that it's not getting into the Script. Is there anything sepecfic I should attempt to Log from inside the Script? Or just a simple message that confirms whether or not it is making it there. The server that this is running on is a dedicated SQL Server. Other than a couple tools installed for SQL (Red Gate SQL Backup and Idera Compliance Manager) which are also on the other systems in question, there is nothing else running. Do you think Process Explorer would be able to help identify what is getting in the way? Other than looking at the package directly (which is on a UNC) I don't really know what else would be getting in the way...--wayne

I'm really not sure what to suggest to detect the true cause of the issue. I would place a Dts.Events.FireInformation as the very first line inside your script, so that you know if it made it in. You can place as many other ones as you like at other points in the script - but again, I just don't think it's making it in there. You may want to place another script immediately before that one, only containing a FireInformation call. Perhaps there's something unusual about the problem script... You may want to double-check this server for a complete/patched installation of .Net, SQL, etc. Perhaps something "didn't get installed right", and that's what's giving you grief. You may want to stress test this particular server to see if any particular combination of load seems to trigger the issue. Take a look at all the Windows Event logs - they may have something in them occurring around the time of your package failure. Talk to me now on

My 2c in: it might be the XML - SSIS relationship in regard to this server (e.g. the 64 bit makes the difference). Please visit http://blogs.msdn.com/b/farukcelik/archive/2010/06/16/why-package-load-error-0xc0010014-in-cpackage-loadfromxml-error-appears-while-trying-to-run-an-ssis-package.aspx and let us know it one of two mentioned there causes is the culprit.Arthur My Blog

In the end I ended up biting the bullet and have two versions of the .dtsx file. One for the 2005 servers and one for the 2008 servers. I'd prefer to only have a single package, but the benefit is that each time a 2008 server runs it, they no longer have to redo the upgrade. So far, since the upgrade, I have not seen any instances of the VSTA error in the SQL Logs.--wayne