Hello, I seem to be getting an error whilst attempting to run a 2005 SSIS package in a newly upgraded (in-place not side-by-side) SQL Server 2008 R2 instance. The package ran in the old 2005 instance (same hardware and OS) but shows this error in the 2008 R2 instance: Message Executed as user: <domain>\SQL. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 14:25:00 Error: 2011-05-10 14:26:05.40 Code: 0x00000003 Source: File Action Task File Action Task Description: There was an exception while loading Script Task from XML: System.ApplicationException: The VSTA designer failed to load: "System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned from a call to a COM component. at VSTADTEProvider.Interop.VSTADTEProviderClass.GetDTE(String bstrHostID, UInt32 dwTimeout) at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.EnsureDTEObject()" at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.EnsureDTEObject() 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: 2011-05-10 14:26:05.71 Code: 0x00000003 Source: File Action Task Description: The Script Task is corrupted. End Error Error: 2011-05-10 14:26:05.73 Code: 0xC0024107 Source: File Action Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:25:00 Finished: 14:26:05 Elapsed: 63.703 seconds. The package execution failed. The step failed. I have tried the trick of restarting the VSTA component using a command prompt: cd "C:\program files (x86)\microsoft visual studio 9.0\common7\ide\" vsta.exe /setup /hostid SSIS_ScriptTask vsta.exe /setup /hostid SSIS_ScriptComponent And this enables the VSTA environment to run when I execute: cd "C:\program files (x86)\microsoft visual studio 9.0\common7\ide\" vsta.exe /hostid SSIS_ScriptTask vsta.exe /hostid SSIS_ScriptComponent But as soon as I run the script again it fails and the environment fails to open again. The SSIS package contains one object which is a Sctipt Task. The package is openable on another server (SQL 2008 R2) but not on the one in question. This implies that there is something missing on my server, but I don't know what. To try to fix this problem I have: Removed Microsoft Visual Studio Tools for Applications 2.0 via Admin tools > Add/Remove Programs and then repair SQL Server 2008 R2. Repair .NET Framework 3.5 SP1. Register VSTADTEProvider.dll and reboot server. Reinstall VSTA using: msiexec /i trin_aide.msi /L*v vsta_setup_log.txt But all of these attempts to resolve my problem have not worked. The frustrating thing here is that I have another server which was built in the same way, but it does not exhibit the reported problem. It seems that the script is killing the VSTA environment. The only solution so far is to manually upgrade the package to SQL 2008 R2 format. This in turn brings versioning difficulties since the same package is used on multiple servers (with different config files) and not all of them have yet been upgraded. This means this would not be my preferred solution if I could fix the failing servers. Any additional ideas on how I can resolve my problem? Mike

I suspect only the script itself became somehow corrupted. Thus, to recover, just drop and recreate this script task, then let's see if it runs. Perhaps you can then distribute this new version?Arthur My Blog

Arthur, I tried removing the script task and then recreating it (pasting the original script back in) and unfortunately I get the same result. Do I need to do anything after removing the script task before recreating it? Any ideas on why the same script would work on some servers and not on others? Mike

Did you try running it off outside VSTA? Say using DTExec as it should be on the server?Arthur My Blog

Arthur, The result of running the dtexex.exe command line is a whole load of errors. The majority of them state: Could not find stored procedure 'sp_dts_addlogentry'. And at the end there is one that says: The task "FileActionTask" can not run on this edition of Integration Services. It requires a higher eddition. The "FileActionTask" is my package i'm trying to run. Is this what you were expecting, and does it help? Mike

This is because you either not connecting to SSIS or you do not have the SSIS installed at all.Arthur My Blog

Yes, that's what the online search suggested. I need to talk to my colleague who conducted the test to see on which server he ran the command on. I had requested he perform the test directly on the server as he was on site, but that doesn't mean that that's what he did. Any ideas on why the packages will work on one server and not another? Mike

I think of one technique you can use to pinpoint the issue. This is the use of DtloggedExec. This tool can diagnose issues and log them even though you do not have the logging implemented in your package. Give it a try and hopefully we find what's wrong with the environment. PS: You may want to follow on how to use it here: http://dtloggedexec.davidemauri.it/How%20to%20use%20DTLoggedExec.ashxArthur My Blog

In the end I upgraded the packages to SSIS 2008 R2 packages and this resolved the problem. It wasn't what I had wanted to do as it'll be a pain to manage during the migration of the other servers, but it's probably better in the long run. Thanks for your input.