We use SQL Server Integration Services (SSIS) for all of our data movement and upgrades. Recently, when running a particular SSIS package, one of our new servers threw a strange error:

“There was an exception while loading Script Task from XML: System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.VisualStudio.Tools.Applications.Core’“

I was on call and one of the first things I did was to cover the basics – checking out the environment. The SSIS package file (.dtsx) was located on the file system exactly where I had expected it to be and hence, I was stumped to see the “File Not Found” exception. The strange thing was that the same package worked fine when run on the staging environment and also in my local development environment.

After checking out various things and a break, I refocused my attention to reading the error message again. That’s when the words simply popped out at me – “…while loading Script Task from XML…” – the script task is where the problem was. Script tasks are most commonly used to set connections on the connection managers and that’s exactly what we were doing.

The problem

Script tasks in SSIS leverage the Visual Studio Tools For Automation (VSTA). I therefore headed over to Add/Remove Programs to confirm that VSTA is indeed installed. I only had the 64-bit version installed on the affected server. As with all things SSIS, on a 64-bit system, I had expected to see both the x64 and the x86 version of VSTA installed. Below is a screenshot of the Add/Remove programs from one of my test VMs which is a 32-bit environment (I could not get the actual screenshot because it was a production server).

Sample VSTA installation on a 32-bit environment (non-production)

The Solution

I immediately contacted the on-call IT team, who kindly shared the SQL Server media from which the installation was done. Just navigating to the following path on the media gave me the 32-bit installable for VSTA. We installed 32-bit version of VSTA and the issue was resolved.

Path on the SQL Server media to get VSTA: ..\redist\VSTA\runtime\x86

Have you ever encountered this or other interesting issues in your deployment experience? How did you troubleshoot them? Do share via the blog comments – I would love to know!

Until we meet next time,
Be courteous. Drive responsibly.