VSTA setup and configuration troubles for SSIS 2008 and R2 installations

VSTA setup and configuration troubles for SSIS 2008 and R2 installations

  • Comments 10

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.

Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post
  • That's great!!! I solved my problem using vsta.exe /hostid SSIS_ScriptComponent... I spended many hours befoure I found you. Thank you.

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

  • I have copied the registry entries from the server working fine to the issue server and then performed the reg cleanup using following it worked fine and issue resolved.

    @rem start batch file

    @rem delete the VSTA registry cache from the current user

    REG DELETE HKEY_CURRENT_USER\Software\Microsoft\VSTA /f

    REG DELETE HKEY_CURRENT_USER\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f

    REG DELETE HKEY_CURRENT_USER\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f

    @rem delete the VSTA registration cache for SSIS script task and data flow script component

    REG DELETE HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VSTAHost\SSIS_ScriptTask /f

    REG DELETE HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VSTAHost\SSIS_ScriptComponent /f

  • How do i get Design Script in SQL Server 2008

  • Doesn't solve my problem. using the following works:

    cd "C:\program files (x86)\microsoft visual studio 9.0\common7\ide\"

    vsta.exe /hostid SSIS_ScriptTask

    vsta.exe /hostid SSIS_ScriptComponent

    But when I try to create a new script component and click edit script, I get this error:

    No template information found. See the application log in Event Viewer for more details.

    When I check the Event Viewer, it states the following:

    The global template information is out of date. Regenerate the templates by running 'VSTA.exe /installvstemplates' or reinstalling the application.  Note: corrective action requires Administrator privileges.

    For more information, see Help and Support Center at go.microsoft.com/.../events.asp

    When I try to run the command (as Administrator) I get the following:

    Command Line is not valid. Unknown Switch : installvstemplates.

    and of course the link to the asp page shows less than nothing.

    Any ideas?

  • @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

  • 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"'

  • It doesn't appear to mentioned above, and maybe it's not mentioned later, but you must be sure and execute this as an administrator.  I kept trying from command prompt, and then realized I needed to set command prompt to "Run as Administrator" under the advanced properties.

  • That's great!!! thank you very much...it solved my problem

    ... :)

  • 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

Page 1 of 1 (10 items)