Using the R data mining model in SSIS

The R code developed in the previous recipe can be used directly in the sys.sp_execute_external_script system stored procedure. You will test this in SSMS. Then you will use the procedure in an OLE DB source inside a SSIS data flow.

Getting ready

In order to continue with this recipe, you need to have R Services (In-Database). This is the installation that integrates R in SQL Server. It includes a database service that runs outside the SQL Server database engine and provides a communication channel between the database engine and R runtime. You install it with SQL Server setup. The R engine includes the open-source R components, and in addition, a set of scalable R packages.

How to do it...

1. First, you need to enable external scripts in SQL Server. In SSMS, execute the following code:

-- Allow external scripts 
USE master; 
EXEC sp_configure 'show advanced options', 1; 
RECONFIGURE 
EXEC sp_configure 'external scripts enabled', 1;  
RECONFIGURE; 
GO 

Note

For...