Execute R script from SSIS Package

Question

I wanted to execute R code from SSIS package. How can I add a data control step that executes R-code? SSIS supports only vb.net and asp.net.

SSIS has many data transformations available but R is very friendly when it comes to data manipulations.

I want to run a R-code from SSIS scripts or some other way.Basically, I'm trying to integrate R in ETL process.

I wanted to extract data(E) from from a CSV file.

Transform (T) it in R and load (L) it in Microsoft database. Is it possible to get this workflow done in SSIS package by executing R-script using SSIS data control items? Thanks!

Answer 1

Here are a couple of ways you could integrate R into your ETL process.

1. Crude, fast and dirty - Execute Process Task in the Control Flow. This would be similar to calling RScript from the command line. You would likely make your transformation, save it to a file on disk, and get that filename from your Execute Process Task so you can feed it into a Data Flow task. Upside is you're keeping your R clean and separate from your C#/VB.

2. Integrated via Rdotnet - You could use the RDotNet library (I believe, haven't tried to integrate it). You would need to register the DLLs in the GAC, and then you can either work with .NET objects in your SSIS scripts or call R scripts directly.

3. Integrated in SQL Server 2016 - Microsoft has added R support via extended stored procedures. You call the R script via stored proc and use a sql query for input data and can store the output. See more detail here. This would mean utilizing an Execute SQL task in SSIS.