SSIS C# Interoperability


One of the things I love most about Microsoft is the deep integration of its vast array of products-its complete and everything talks to everything else and that’s what you want if you’re chasing automation and productivity-the ability to integrate and orchestrate various processes. The other thing of course is the vast documentation (and helpful community) and ease with which things work, the very things that makes it the leader in the Gartner Magic Quadrant when it comes to the BI space- the ‘completeness of vision’ and ‘ability to execute’.

One of the things I’ve been working on recently that perfectly illustrates this point is a C# library (assembly) that not many people would have seen. It’s actually part of the SQL Server SDK for software developers. What I like about this solution is apart from solving a complex technical problem in a very short time is how little effort I had to put into it to bridge two different technologies together and have things working and ‘productionised’ in a very short time. Specifically, the solution entails integrating a SSIS package with a C# Console Application that calls a series of Tableau REST APIs to load a SQL database (8 tables). So the app calls API, API loads tables and app then calls SSIS which then blends and transforms the data-all in the press of a button. The best thing of course is you can monitor the progress of the SSIS package through the events sent by the handler back to your app (amongst other things including return values).

Why would we want to do that? In a nutshell, two separate processes with different layers of permission in disparate systems on different domains. C# does the complex, SSIS does the volumes. Rather than going into the why, I’ll  focus on the how but needless to say, a console app is simply much more flexible to build, debug and maintain than a SSIS package that tries to do  all the above in one package and my preference if scheduling is not needed. This is particularly true if your app contains various assemblies imported from Nuget-it can get very complicated in trying to import all those external libraries into your SQL box and debugging it!

The code below for the integration bit is largely from MSDN documentation that you can download here. As you can see, I simply copied and paste most of the code into Linqpad as I noted in an earlier blog about rapid prototyping and discovery- refactoring comes later if at all. I’ll just provide a high level overview as everything needed is covered in the Microsoft documents including code, namespace and other info. The only thing to note is the example in the documentation was written for earlier versions of SQL Server (if you’re on 2016). The DLLs are the same, you just need to pick from the correct folder.

This screenshot directly below is all there is to it as far as the SSIS integration code is concerned-Simply load the SSIS package and register the event Listener to monitor your events from SSIS. Take note of the output in the debugger as I write out the ‘OnProgress’ events.


Now compare and notice these are the same events firing within SSIS inside Visual Studio.


In my console app, I’ve only registered two events ,OnProgress and OnError. I could just as easily register all the other inbuilt events from SSIS including my favourite, OnVariableValueChanged, which would have returned my variable values during run-time. The shot below shows all the events available in SSIS.

Take note of where the DLLs are for different versions of SQL. Notice I’m using version 130 (for SQL 2016) and that these are now part of the SQL SDKs (pretty sure they were standard in earlier versions of SQL Server).


And finally, integration with SQL Jobs. These code below are not part of the SSIS library above but included here as it is a similar approach to automation with C#. IF you’re from a SQL background, you may notice I’m simply running one of the system stored procs to start a job


And here, calling another proc to get the outcome of the job (also seen above in the debugger)


Notice the same code/proc as called directly from SQL/SSMS and the same results that come back from that call- four tables returned from the dataset for all the details about your scheduled job


So there you have it, another example of why Microsoft is in a league of it’s own when it comes to integration. In my next blog, I’ll show you some other less known examples from the SDK, including various return value types for your apps to consume.

Stayed tuned.


Leave a Reply