VSTA setup and configuration troubles for SSIS 2008 and R2 installations

Article
08/18/2010

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?

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

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.

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