I'm trying to use a script tranformation in a data flow but i can't open the editor!

nothing happens in SSDT when i click the edit script button

I noticed the following in the event viewer which i'm guessing is what's happening

The description for Event ID 0 from source VSTA cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event: 

devenv(5688): Error: System.ComponentModel.Win32Exception (0x80004005): The system cannot find the file specified
   at System.Diagnostics.Process.StartWithShellExecuteEx(ProcessStartInfo startInfo)
   at System.Diagnostics.Process.Start()
   at System.Diagnostics.Process.Start(ProcessStartInfo startInfo)
   at Microsoft.VisualStudio.Tools.Applications.IdeCommunications.IdeServer.Launch()
   at Microsoft.VisualStudio.Tools.Applications.IdeCommunications.IdeCommunicationsServer.Microsoft.VisualStudio.Tools.Applications.IdeCommunications.External.IIdeCommunications.Launch() 1/09/2015 5:21:14 PM

the message resource is present but the message is not found in the string/message table

Any tips on what to do?

the other weird thing is that it's SSDT for VS2013, but the editor shows it's trying to run "Microsoft Visual C# 2012" as the scripting language. And I can't change it to J# or VB.NET (or whatever the other options are)

Hi Jakbuk,

Try the following command script to clean up cached VSTA configurations for SSIS . From a command prompt (under WoW32 on 64 bit platforms) with admin privileges (make sure to run the command prompt as an administrator if you have UAC enabled) run a batch file with the following content:

@rem start batch file
@rem delete the VSTA registry cache from the current user
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 delete the VSTA registration cache for SSIS script task and data flow script component
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f

@rem delete the cached files from the HDD
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"

@rem re-generate the VSTA files for SSIS script task and component
"%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe" /hostid SSIS_ScriptTask /setup
"%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe" /hostid SSIS_ScriptComponent /setup

@rem end batch file

Hope it helps,

Hi Jakub,

As for the first question, it can be caused by missing required earlier 2012 version of VS tools or permission issue. For more details, please refer to the following similar thread:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1b003307-e7d8-4975-a798-611b957a7cc3/script-transformation-editor-wont-open-in-data-tools-bi?forum=sqlintegrationservices

As to your second question, the Script Transformation component uses Microsoft Visual Studio Tools for Applications (VSTA) as the environment in which you write the scripts. VSTA only support the Microsoft Visual C# 2012 and Microsoft Visual Basic 2012 programming languages. Once we click the Edit Script button in the component, the value of ScriptLanguage property is fixed, cannot switch it anymore.

Thanks,
Katherine Xiong

Hi Katherine,

I saw those links and tried running as admin - made no difference.

I've also disabled the revocation check - no difference

I tried repairing the 2014 ssdt-bi install - no difference

can you provide a link to the specific file(s) i need to install? I have 2008r2 bids, 2012 ssdt and 2014 ssdt-bi installed on this machine.

Milan, i tried that batch script but got a bunch of errors

F:\VXM\GPA>REG DELETE HKCU\Software\Microsoft\VSTA /f
The operation completed successfully.

F:\VXM\GPA>REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
The operation completed successfully.

F:\VXM\GPA>REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f
The operation completed successfully.

F:\VXM\GPA>REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
ERROR: The system was unable to find the specified registry key or value.

F:\VXM\GPA>REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f
ERROR: The system was unable to find the specified registry key or value.

F:\VXM\GPA>rd /s /q "C:\Users\jkmelbs\AppData\Roaming\Microsoft\VSTA"

F:\VXM\GPA>rd /s /q "C:\Users\jkmelbs\AppData\Roaming\Microsoft\VSTAHost\SSIS_Sc
riptTask"

F:\VXM\GPA>rd /s /q "C:\Users\jkmelbs\AppData\Roaming\Microsoft\VSTAHost\SSIS_Sc
riptComponent"

F:\VXM\GPA>rd /s /q "C:\Users\jkmelbs\Local Settings\Application Data\VSTA"
The system cannot find the file specified.

F:\VXM\GPA>rd /s /q "C:\Users\jkmelbs\Local Settings\Application Data\VSTAHost\S
SIS_ScriptTask"
The system cannot find the path specified.

F:\VXM\GPA>rd /s /q "C:\Users\jkmelbs\Local Settings\Application Data\VSTAHost\S
SIS_ScriptComponent"
The system cannot find the path specified.

F:\VXM\GPA>"C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe" /
hostid SSIS_ScriptTask /setup
The system cannot find the path specified.

F:\VXM\GPA>"C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe" /
hostid SSIS_ScriptComponent /setup
The system cannot find the path specified.

I found the vsta.exe in program files(x86), but that's dated 2009? surely that's the wrong version? I thought vs 9 was used for 2008r2?