如何通过C#脚本将SSIS任务中的包数据导出至Application Insights？

Absolutely! You can totally make this work using a C# Script Task in SSIS. Let me walk you through the approach, step-by-step setup, and a practical code example to get your SSIS package data flowing into Application Insights.

The core idea is to use SSIS's Script Task (C# language) to integrate with Azure Application Insights' SDK. We’ll capture key SSIS package execution data—like run status, error details, timestamps, and even custom business metrics—then send that data directly to Application Insights for monitoring, analysis, and alerting.

• Add a Script Task to your SSIS control flow: Drag the Script Task onto your control flow canvas, then select C# as the scripting language when prompted.
• Install the Application Insights SDK in the script project: Open the script editor, launch the NuGet Package Manager, and install the Microsoft.ApplicationInsights package. This gives you the tools to send telemetry data to Application Insights.
• Securely store your Instrumentation Key: Never hardcode your Application Insights Instrumentation Key! Use an SSIS package variable to store it—this makes it easy to update across environments without modifying code.
• Write C# logic to capture and send data: Access SSIS runtime data via the Dts object, structure it into telemetry events/traces, and send it to Application Insights.

Here’s a simplified, working example that captures basic package execution info and sends it to Application Insights:

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.ApplicationInsights;
using Microsoft.ApplicationInsights.DataContracts;

namespace ST_XXXXXXXXX
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            try
            {
                // Pull Instrumentation Key from SSIS variable
                string instrumentationKey = Dts.Variables["User::AI_InstrumentationKey"].Value.ToString();
                
                // Initialize the Application Insights client
                var telemetryClient = new TelemetryClient { InstrumentationKey = instrumentationKey };
                
                // Capture core SSIS package metadata
                string packageName = Dts.Variables["System::PackageName"].Value.ToString();
                DateTime startTime = (DateTime)Dts.Variables["System::StartTime"].Value;
                string executionStatus = Dts.TaskResult == (int)DTSExecResult.Success ? "Success" : "Failed";
                
                // Create a custom telemetry event for package execution
                var packageExecutionEvent = new EventTelemetry("SSIS Package Execution");
                packageExecutionEvent.Properties.Add("PackageName", packageName);
                packageExecutionEvent.Properties.Add("ExecutionStatus", executionStatus);
                packageExecutionEvent.Properties.Add("StartTime", startTime.ToString("yyyy-MM-dd HH:mm:ss"));
                packageExecutionEvent.Properties.Add("EndTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                
                // Capture error details if the package failed
                if (Dts.TaskResult == (int)DTSExecResult.Failure)
                {
                    string errorMessage = Dts.Variables["System::ErrorDescription"].Value.ToString();
                    packageExecutionEvent.Properties.Add("ErrorMessage", errorMessage);
                    
                    // Send a separate exception trace for deeper debugging
                    telemetryClient.TrackException(new Exception(errorMessage));
                }
                
                // Send the telemetry data
                telemetryClient.TrackEvent(packageExecutionEvent);
                telemetryClient.Flush(); // Ensure data is sent immediately (critical for short-running packages)
                
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {
                // Log script errors to SSIS (you can also send this to Application Insights if needed)
                Dts.Events.FireError(0, "SSIS to AI Script Task", ex.Message, string.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

• .NET Version Compatibility: Ensure your SSIS Script Task targets a .NET Framework version compatible with the Application Insights SDK (4.7.2 or newer is recommended).
• Error Handling: Add robust exception handling to avoid breaking your SSIS package if telemetry sending fails (unless you want package success tied to AI integration).
• Custom Metrics: Expand the script to capture business-specific data—like rows processed, validation pass rates, or execution duration—to get deeper insights in Application Insights.
• Security: Use SSIS variables, environment variables, or Azure Key Vault to store your Instrumentation Key (never hardcode it).
• Telemetry Flush: Always call telemetryClient.Flush() after sending data to ensure it doesn’t get stuck in the buffer, especially for short-running packages.

内容的提问来源于stack exchange，提问作者Joey