Thursday, October 29, 2015

Quick PowerShell Script for getting JSON.NET working with SQL 2012 Data tools on Windows 2012

Quick Description:  SQL Server Data Tools for SQL 2012 on Windows 2012 has JSON.NET installed, but can't find it at runtime.

NewtonSoft's super-awesome, saved my life JSON.NET Framework for .NET is so solid, but the SQL 2012 script component on 2012 is temperamental about remembering it's installed (when installed with NuGet.  On older versions of windows, the solution is to run a command-line GAC, but this proves difficult on Windows 2012.

What Worked for Me:
Script shamelessly (and gratefully) cribbed from this excellent TechNet article on using PowerShell to generally GAC a .dll on Windows 2012.

Download the package to the machine
Expand the package and drop the files to their final location

In PowerShell 2.0, for a script component referencing .Net 4.0 (the default for SQL 2012 Data Tools), this would be the script example.

[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")          
$publish = New-Object System.EnterpriseServices.Internal.Publish          

SQL/SSIS Data Tools Script Component Editor Doesn't Launch on SQL 2012/Windows 2012

Short Description:  When attempting to launch the script editor in an SSIS script component in SSIS Data Tools for 2012 on Windows 2012 a user in the local Admin user group gets the error Cannot show Visual Studio 2010 Tools for Applications Editor.

Full error message:  could not load file or assembly

Environment Note:  This was a SQL server dev box originally delivered without Data Tools.  It seems to be common thread with people reporting this issue that Data Tools was installed after the initial install of SQL 2012.

Things to Try First:
Try the solution in the link in this connect article first.  It solves almost everyone's problem (particularly if you are on Sql 2012 on an older OS, or migrating packages from SQL 2008 to SQL 2012 Data Tools).

What Was Different About My Environment:
My environment is Windows 2012/SQL 2012.  As of the current date, most of the reports of this issue are on Windows 2008R2/SQL 2012.

What Worked:
If after attempting to reinstall Data Tools and SSIS in the correct same order it still does not work, attempt to install the redists (below) for VSTA and the VisualStudioShell.  On 2012, even as an administrator running explorer in administrator mode this may not go well.  

The install files should be in 
  • %SQL 2012 install%\redist\VSTA\runtime\x86
  • %SQL 2012 install%\redist\VSTA\runtime\x64
  • %SQL 2012 install%\redistVisualStudioShell\VC10SP1\x86
  • %SQL 2012 install%\redist\VisualStudioShell\VC10SP1\x64
Right click the .msi file and select install (run all of these, you need both x86 and x64 for development and unit testing).  If you get a message that it can't find the file, (even though it's right there and you just clicked it, you need to make some changes).

Assuming this is a dev/sandbox, and you have administrator permissions, you just need to make a couple of changes, and then install the redistributables and you're home free!  
I also set the user account type to administrator in user accounts, but I'm not confident that had much to do with it.

If you're not an admin:
I'm reasonably confident the permissions are required for the installation, and not the launch of the script component, so if you're not an admin of the machine (I haven't tested that), it would be worth asking the admin to go through the steps.  The rest of SQL 2012 installs fine on windows 2012 even if installed after the initial install.