Leveraging C# within PowerShell for Advanced ETL Operations in SQL Server

Introduction

The ability to efficiently perform Extract, Transform, Load (ETL) operations is essential for database administrators and developers, particularly those working with data warehouses and others with large data transformation. While PowerShell is a powerful tool in its own right for database management, embedding C# within PowerShell scripts can significantly enhance your ETL processes. This blog post will guide you through a practical example of using C# code within a PowerShell script to perform ETL operations on SQL Server tables. We’ll start with extracting data from a SQL Server, followed by transforming it using C#, and finally, loading it back into a different table.

Step 1: Extract Data from SQL Server

Our first step in the ETL process is to extract data from the SQL Server. This phase involves using standard PowerShell code to establish a connection with the SQL Server and retrieve the necessary data. Here’s how you can do it:

$connectionString = "Server=your_server;Database=your_database;Integrated Security=True;"
$query = "SELECT * FROM SourceTable;"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
$dataSet = New-Object System.Data.DataSet
$adapter.Fill($dataSet)
$connection.Close()

The script starts by defining the connection string to your SQL Server and prepares a SQL query to fetch the data. The New-Object System.Data.SqlClient.SqlConnection and subsequent lines are used to establish the connection and execute the query.

Step 2: Transform Data Using C#

After extracting the data, the next step in our ETL process is to transform it. This is where we leverage the power of C# within our PowerShell script. PowerShell’s Add-Type cmdlet allows us to embed C# code, offering us more sophisticated data manipulation capabilities. Here’s an example:

Add-Type -TypeDefinition "@
using System;
using System.Data;
using System.Xml.Serialization;
public class DataTransformer
{
    public static DataTable TransformData(DataTable dt)
    {
        string colName1 = "Column1";
        string colName2 = "Column2";

        if (!dt.Columns.Contains(colName1) || !dt.Columns.Contains(colName2))
        {
            throw new ArgumentException("Specified columns do not exist in the DataTable");
        }

        dt.Columns.Add("NewColumn", typeof(string));
        foreach (DataRow row in dt.Rows)
        {
            row["NewColumn"] = row[colName1].ToString() + row[colName2].ToString();
        }
        return dt;
    }
}
"@ -ReferencedAssemblies "System.Data", "System.Xml"

try {
    $transformedData = [DataTransformer]::TransformData($dataSet.Tables[0])
} catch {
    Write-Error "Error in data transformation: $_"
    return
}

This script defines a C# class DataTransformer with a static method TransformData. This method performs the transformation by iterating through each DataRow and modifying or adding new columns as necessary.

Step 3: Load Transformed Data Back into SQL Server

The final step in our ETL process involves loading the transformed data back into a different table in the SQL Server. This step utilizes PowerShell’s capabilities to handle SQL Server operations efficiently. Here’s the script for this step:

$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($connectionString, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
$bulkCopy.DestinationTableName = "DestinationTable"
$connection.Open()

if ($transformedData -is [System.Data.DataTable]) {
    $bulkCopy.WriteToServer($transformedData)
} else {
    Write-Error "Transformed data is not in the correct format for bulk copy."
}

$connection.Close()

The SqlBulkCopy class is used to perform a bulk data load into the SQL Server. The script creates a new instance of SqlBulkCopy, specifies the destination table, and then loads the transformed data using the WriteToServer method.

Conclusion

In this blog we’ve explored how to effectively combine PowerShell and C# to perform advanced ETL tasks on SQL Server. By leveraging PowerShell for data extraction and loading, and C# for complex data transformations, we can create powerful and efficient data processing workflows. This approach is particularly useful for SQL DBAs and developers looking to enhance their data handling capabilities beyond what’s possible with standard SQL tools. Remember, while this example provides a basic framework, real-world applications may require additional error handling, optimization, and customization to fit specific needs.