Installing R packages in SQL Server using only T-SQL

Installing R packages in SSMS using T-SQL can take some time, especially when you need to switch between R Tools for Visual Studio or R Studio or your favorite R GUI tool (or R CMD). But so far, installing any additional library using sp_execute_external_script is just not working.

--InstallPackage using sp_execute_external_script
EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'install.packages("ggplot2")'
WITH RESULT SETS (( ResultSet VARCHAR(50)));

-- using Download.file command
EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'download.file("https://cran.r-project.org/bin/windows/contrib/
3.4/ggplot2_2.1.0.zip","ggplot2")
                 install.packages("ggplot2", repos = NULL, type = "source")'
WITH RESULT SETS (( ResultSet VARCHAR(50)));

Both executions yield in error messages. Especially the first one would be great way of installation. At this point, I would not find a way of just passing this command to install the package.

Julie Koesmarno made a great post on installing R packages. Please follow this post. Also Microsoft suggests the following way to install R packages on MSDN.

Since I wanted to be able to have packages installed directly from SQL Server Management Studio (SSMS) here is yet another way to do it. I have used  xp_cmdshell to install any additional package for my R (optionally you can set EXECUTE AS USER).

USE WideWorldImporters;
GO
-- enable xp_cmdshell
EXECUTE SP_CONFIGURE 'xp_cmdshell','1';
GO

RECONFIGURE;
GO
EXEC xp_cmdshell '"C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER
\R_SERVICES\bin\R.EXE" cmd -e install.packages(''tree'')';  
GO

in this case I have installed package tree by calling Microsoft R Services with -e switch and adding straight R code.

Results are:

It might not be the proposed way, but when completing T-SQL and R code it is just a nice way with little annoyances.  I would still suggest the way Microsoft proposed or Julie described on her blog, but if you feel confident, you can use this method.

Code is available at Github.

Happy R-SQLing!