SQL Server CLR Integration and SSIS Automation with C#

In modern enterprise applications, developers often face the challenge of integrating application logic with complex database operations and data movement pipelines. Two powerful features from the Microsoft SQL Server ecosystem can help bridge this gap effectively.

• SQL Server CLR Integration: Leverage .NET capabilities within SQL Server for advanced procedural logic.
• SSIS Automation in C#: Programmatically control and automate ETL pipelines using SQL Server Integration Services (SSIS).

This article explores both concepts in depth, providing code examples, use cases, and best practices.

SQL CLR Functions in .NET: Embedding Business Logic in SQL Server

SQL CLR (Common Language Runtime) integration allows developers to create stored procedures, functions, aggregates, and triggers using any .NET language (like C#). This is particularly useful when T-SQL falls short for tasks requiring procedural logic, complex math, string operations, or external library support.

Example: A Simple CLR Scalar Function in C#

[SqlFunction]
public static int AddNumbers(int a, int b)
{
    return a + b;
}

After compiling this function into a DLL and registering it with SQL Server, it can be invoked just like a built-in T-SQL function.

SELECT dbo.AddNumbers(100, 250); -- Returns 350

Step-by-Step Deployment Process

Enable CLR in SQL Server

sp_configure 'clr enabled', 1;
RECONFIGURE;

Compile the C# code into a Class Library (DLL)

• Use Visual Studio to create a Class Library project.
• Set the project to target .NET Framework, not .NET Core.

Deploy the Assembly to SQL Server.

CREATE ASSEMBLY MyClrAssembly
FROM 'C:\Path\To\MyClrAssembly.dll'
WITH PERMISSION_SET = SAFE;

Create the Function.

CREATE FUNCTION dbo.AddNumbers(@a INT, @b INT)
RETURNS INT
AS EXTERNAL NAME MyClrAssembly.[YourNamespace.YourClass].AddNumbers;

When to Use SQL CLR Functions?

Use Case	Why Use CLR
Complex mathematical operations	.NET has richer math libraries
String and regex manipulation	.NET handles regex far better than T-SQL
File system or external access	Use with EXTERNAL_ACCESS permission
Code reusability	Centralize shared logic across apps & DB

Note. Use CLR sparingly for security and performance. Avoid overusing it for tasks that T-SQL handles well.

Automating ETL with SSIS from C#: Taking Control of Data Pipelines

SQL Server Integration Services (SSIS) is a widely used tool for ETL (Extract, Transform, Load) processes. While it’s typically run via SQL Agent jobs or the SSIS catalog, sometimes you need tighter control — dynamic execution, real-time monitoring, or conditional branching based on application logic.

Example: Running a Package from C#

using Microsoft.SqlServer.Dts.Runtime;

Application app = new Application();
Package package = app.LoadPackage(@"C:\Packages\MyPackage.dtsx", null);
DTSExecResult result = package.Execute();

if (result == DTSExecResult.Success)
{
    Console.WriteLine("Package executed successfully.");
}
else
{
    Console.WriteLine("Package execution failed.");
}

What You Can Automate with This?

• Trigger SSIS packages based on real-time events (like user actions, webhooks, or workflows).
• Dynamically select packages, connections, or parameters based on app logic.
• Integrate with logging and monitoring systems for auditing ETL runs.
• Schedule or queue package runs without using SQL Agent.

Requirements & Tips

Requirement	Details
SSIS Runtime	Ensure Microsoft.SqlServer.ManagedDTS is referenced.
Permissions	App/service needs rights to run SSIS and access packages.
DTSX Package Availability	Ensure the package path is correct and accessible.
SQL Server Data Tools (SSDT)	For creating and debugging SSIS packages.

You can also manipulate variables, log events, and receive task-level execution results via the SSIS object model in C#.

Combining CLR + SSIS for End-to-End Automation

By using both CLR integration and SSIS automation in your application stack, you unlock powerful data and logic orchestration capabilities.

Practical Scenario

Imagine a financial reporting system.

• You use SQL CLR functions to calculate custom interest models in queries.
• You automate SSIS to pull raw transaction data nightly and load into your analytics warehouse.
• Your C# application coordinates both — triggering ETL, monitoring outcomes, and presenting results in dashboards.

Security and Best Practices

• Avoid UNSAFE permissions unless absolutely necessary for SQL CLR.
• Use strong-named assemblies for CLR to prevent version conflicts and security risks.
• Secure your package execution by using Windows authentication or proxy credentials in SSIS.
• Isolate configuration: Read SSIS parameters from external configuration files or variables, not hardcoded paths.

Summary: Why This Matters

Feature	Benefits
SQL CLR Integration	Reuse .NET logic, enhance SQL performance, simplify complex operations
SSIS Automation in C#	Real-time control over ETL, seamless integration with business logic

These technologies help you create agile, intelligent, and integrated data systems — essential in today’s data-driven applications.

Final Thoughts

SQL Server isn't just a database — it’s a platform for building smart, automated systems that react and scale with your application. Using CLR integration and SSIS automation, developers can tightly couple database processing with business workflows, reduce manual effort, and deliver greater value through code.

Ready to modernize your data workflows? Combine your C# skills with the power of SQL Server for next-level automation.

Full Class Example

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts.Runtime;

namespace SqlServerIntegration
{
    public class SqlServerIntegrationHelper
    {
        /// <summary>
        /// SQL CLR function to add two numbers.
        /// Can be registered in SQL Server as a UDF.
        /// </summary>
        [SqlFunction]
        public static SqlInt32 AddNumbers(SqlInt32 a, SqlInt32 b)
        {
            return a + b;
        }

        /// <summary>
        /// Executes an SSIS package from a given .dtsx file path.
        /// Returns true if successful, false otherwise.
        /// </summary>
        /// <param name="packagePath">Full path to the .dtsx package file</param>
        /// <returns>True if successful, false if failed</returns>
        public static bool ExecuteSSISPackage(string packagePath)
        {
            try
            {
                Application app = new Application();
                Package package = app.LoadPackage(packagePath, null);

                DTSExecResult result = package.Execute();

                if (result == DTSExecResult.Success)
                {
                    Console.WriteLine("✅ SSIS Package executed successfully.");
                    return true;
                }
                else
                {
                    Console.WriteLine("❌ SSIS Package execution failed.");
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"⚠️ Error executing SSIS Package: {ex.Message}");
                return false;
            }
        }

        // Optional: Main method for standalone testing (Console App only)
        public static void Main()
        {
            Console.WriteLine("Running SSIS Package...");
            string path = @"C:\Packages\MyPackage.dtsx"; // Change this to your actual path
            ExecuteSSISPackage(path);
        }
    }
}