Using “C# Interactive” aka CSI/CSX for ETL

motivation

interactive C# offers typical REPL benefits ala powershell without the mental context switch required to leave our beloved C# syntax =)
 

notable

  • great MSDN reference article
  • CSX syntax can be executed from either Visual Studio 2015 (as of update 1) > View > Other Windows > C# Interactive
  • -or- C:\Program Files (x86)\MSBuild\14.0\bin\csi.exe
    • then #load file.csx

Extract

this example is based on a low fidelity web page as the raw data source
… it’s really nice to have all the convenient one liner RESTy methods of System.Net.WebClient available now vs the lower level WebRequest/WebResponse pattern that came with .Net 1.0. E.g. client.DownloadString, DownloadFile, etc.

Transform

trivially demonstrative in this case, simply a string.Split call 🙂

Load

demonstrates leveraging SQL Server’s Table-Valued-Parameter functionality to bulk upload rows which are then conveniently manifested inside the receiving stored procedure as a standard sql rowset, ready for tpyical DML like joining to other tables, etc.
 

SQL definition

scrape.csx