VSTA setup and configuration troubles for SSIS 2008 and R2 installations

  • Article

SSIS 2008 Uses VSTA 9.0 to edit Script Tasks and Script Task (in Control Flow) and Script Components (in Data flow tasks). VSTA stands for Visual Studio for Team Applications.

It should look like this…  and when you click the “Edit Script…” button the Script editor VSTA windows opens when it works (which is most of the time). But when it doesn’t what can you do?

image

A quick way to isolate any problems when clicking the “Edit Script” button to check if VSTA is setup properly outside of SQL Server Integration Services is by running the following commands at the command prompt.

 

SQL 2008:

cd "C:\program files (x86)\microsoft visual studio 9.0\common7\ide\"
vsta.exe /hostid SSIS_ScriptTask
vsta.exe /hostid SSIS_ScriptComponent

image

If the above two commands work, you’ll see a blank VSTA window with no open project. If that works, expect the VSTA designer to open for both a SSIS script task and script component.

If it doesn’t work not, I expect a severe error, or the error which says “Registration information for this application needs to be updated. To update, log on as an administrator and run this command:….”

Another error we got… and how we repaired the installation

=========
TITLE: Microsoft Visual Studio
------------------------------
Cannot show Visual Studio 2008 Tools for Applications editor.
------------------------------
ADDITIONAL INFORMATION:
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()" (Microsoft.SqlServer.VSTAScriptingLib)
=================

Since SSIS uses VSTA 2.0, we need to fix that one only if there is SSIS trouble. Visual Studio doesn’t really related to the VSTA install. A little background info I got from the VSTA product group on who exactly installs VSTA:
- Visual Studio 2008 doesn’t install VSTA, it will install shared components only.
- Office - InfoPath installs VSTA 1.0 which will live SxS with VSTA 2.0
- SQL Server 2008 installs VSTA 2.0 for SSIS

If you have problems opening VSTA inside BIDS to edit SSIS scripts… then try one of these methods:

A. Uninstalling SQL Server 2008 and Reinstalling it may fix the problem.

B. If you can't do that try the manual approach below.... credit to SSIS script guru Silviu Guea for helping me figure this stuff out!

{
The below examples are for x64 only.... remove the (x86) in below paths if running on a 32-bit machine

1. If VSTA won't open then I’ve used the following script (ran by an admin account) to be able to write to HKLM root) to repair SQL type VSTA installations in the past with very good results:

A. Make a registry backup just in case we mess something up! You can export HKEY_LOCAL_MACHINE and HKEY_CURRENT_USER for this exercise:

B. Make a .bat file from the following text. Run from the command prompt with Admin permissions. This file will delete the keys (reg delete) mentioned, and also remove various directories (rd) related to loading SSIS settings in VSTA.

For SQL 2008:
rem replace HKCU\Software\Microsoft with HKCU\Software\Wow6432Node\Microsoft for x64 machines
REG DELETE HKCU\Software\Microsoft\VSTA /f
REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f
rem replace HKLM\Software\Microsoft with HKLM\Software\Wow6432Node\Microsoft for x64 machines
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f

rem Some of these deletes may fail, which is OK
rd /s /q %AppData%\Microsoft\VSTA
rd /s /q %AppData%\Microsoft\VSTAHost\SSIS_ScriptTask
rd /s /q %AppData%\Microsoft\VSTAHost\SSIS_ScriptComponent
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTA"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTAHost\SSIS_ScriptTask"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTAHost\SSIS_ScriptComponent"

 
C. Followed by the following commands to re-register SQL Server Integration Services components:

SQL Server 2008:
cd "C:\program files (x86)\microsoft visual studio 9.0\common7\ide\"
vsta.exe /setup /hostid SSIS_ScriptTask
vsta.exe /setup /hostid SSIS_ScriptComponent

This VSTA process may run for a few minutes.

Check Task Manager Processes tab until you see vsta.exe*32 disappear before proceeding.

D. Test again from a command prompt:

SQL 2008:
cd "C:\program files (x86)\microsoft visual studio 9.0\common7\ide\"
vsta.exe /hostid SSIS_ScriptTask
vsta.exe /hostid SSIS_ScriptComponent

Note any new errors. It should show a progress bar saying to configuring VSTA for the first time

E. Try SSIS if no new errors are raised in step D.

F. Some errors may require VSTA to be reinstalled. VSTA is part of SQL 2008 setup files if you need to run it separately.
The MSI file we can run separately is on the SQL 2008 media: \SQL Server 2008\x86\Setup\trin_aide.msi

msiexec /i trin_aide.msi /L*v vsta_setup_log.txt

}

Yet another error we got… and how we repaired the installation

In another situation I couldn't get Setup to complete successfully because of VSTA corruption due to missing registry keys...

Setup during reinstall would keep failing on numerous steps (Tools, BIDS, SSIS, etc)

The error was:
TITLE: Microsoft SQL Server 2008 Setup
The following error has occurred:
Cannot open registry path 'SOFTWARE\Microsoft\VSTA\9.0'

TITLE: Microsoft SQL Server 2008 Setup
The following error has occurred:
Cannot read registry value 'InstallDir' under 'SOFTWARE\Microsoft\VSTA\9.0'

1. Using regedit, we found the above key 'SOFTWARE\Microsoft\VSTA\9.0'missing. Added it back, but still so many values and subkeys were missing.

On x64 machines, the key is HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VSTA

2. I exported the VSTA key from a working machine with same bitness (x64 or x86) and same drive structure/program files paths for VSTA.

3. By clicking on the exported .reg file, I imported those keys onto the failing machine. I uninstalled the Shared Components in SQL Server setup, and reinstalled the Shared Components in SQL Server 2008 setup. Now it works on that machine.

image

Note: Removed SQL 2012 (Denali) information since it was only applicable to the beta timeframe before the dependency on VSTA.exe was removed.

Comments

  • Anonymous
    December 03, 2010
    That's great!!! I solved my problem using vsta.exe /hostid SSIS_ScriptComponent... I spended many hours befoure I found you. Thank you.

  • Anonymous
    May 12, 2011
    Thank you, cleaning the registry and calling VSTA setup once again solved my problem with failues when trying to edit a Script Task.

  • Anonymous
    September 21, 2011
    The comment has been removed

  • Anonymous
    October 03, 2011
    How do i get Design Script in SQL Server 2008

  • Anonymous
    October 06, 2011
    The comment has been removed

  • Anonymous
    October 07, 2011
    @MajikTom is it affecting VSTA shell for scripting or the original shell DevEnv itself? This blog tells how to reset templates for DevEnv blog.laksha.net/.../no-visual-studio-template-information.html And there is a similar thread here social.msdn.microsoft.com/.../f51618c2-1aee-4e6e-8934-0ed1b50704c3

  • Anonymous
    August 20, 2013
    Many thanks - first google hit for my error, your first cmd solution solved it. FTR, error was 'The vsta designer failed setup for HOSTID "SSIS_ScriptComponent"'

  • Anonymous
    October 09, 2013
    The comment has been removed

  • Anonymous
    January 13, 2014
    That's great!!! thank you very much...it solved my problem ... :)

  • Anonymous
    January 22, 2014
    I ran part C after the .bat file, It opened a blank page for the .../hostid SSIS_ScriptComponent but when I run the "vsta.exe /hostid SSIS_ScriptTask" still gave me the same error of "Registration need to be updated..." My issue is that I imported an SSIS file from an old SQL Server 2005 where it was working to an upgraded SQL Server 2008R2, but it seems when I open the file in BIDS, it gives me the registration error... please help, Thanks

  • Anonymous
    January 19, 2016
    I spend lot's of hours on this, finally this solution work for me. Thank you :) Keep posting

    • Anonymous
      February 10, 2017
      I've tried many things for 6 hours including reg & dereg in a bat files from the note above. What worked for me was i ran vsta by itself and rest the import & export settings in the tools menu. It still gave me that the host and script was not loaded but when I went into the vs studio 2008 and clicked on edit a script, it worked.
      • Anonymous
        February 10, 2017
        reset the import & export...spelling error above

  • Anonymous
    July 12, 2016
    So....Another solution was...1) Run the regclean steps Jason mentioned above. (mine failed when it got to addressing vsta.exe) 2) repair SQL Server 2008 (add/remove programs)2a) When asked to select the server to repair drop-down and select <associated processes.(not exact name but it's the only choice that's not a server name)3) let repair run its course and you should be OK if your opportunity is similar to mine. Best of luck.

  • Anonymous
    September 06, 2016
    running "vsta.exe /hostid SSIS_ScriptComponent" command worked for me. Other blogs didn't help. Thanks a lot!!