SSIS 2008脚本任务运行报错：无法加载脚本执行，寻求解决方案

Hey there, let's work through this SSIS 2008 issue together. That "Cannot load script for execution" error is tricky because it pops up even when your script builds fine in the editor—usually it's not about your code logic, but environment or configuration quirks. Let's break down the fixes first, then give you a solid script to handle that folder creation task.

Common Causes & Fixes for the Load Error #

1. .NET Framework Version Mismatch #

SSIS 2008 is tied to .NET Framework 3.5 by default. If your script project is targeting a newer version (like 4.0+), the editor might let you build it, but SSIS can't load it at runtime.

• How to check/fix:
  • Open the script editor, right-click on the script project (the one containing ScriptMain.cs) → Properties.
  • Go to the Application tab, set Target framework to .NET Framework 3.5.
  • Save and rebuild the script.

2. Permissions on SSIS Temp Files #

SSIS compiles your script to a temporary folder (typically C:\Users\<YourAccount>\AppData\Local\Temp\VSTA). If the account running your SSIS package doesn't have read/write access here, the script fails to load.

• Quick fixes:
  • Run SQL Server Data Tools (BIDS) as an administrator when editing/running the package.
  • Verify the runtime account has permissions to that Temp/VSTA directory.

3. Unnecessary External References #

If your script references non-default .NET assemblies (like custom DLLs), SSIS might not find them at runtime. Stick to the core libraries that come with .NET 3.5 for SSIS 2008.

• Fix: Remove any extra references you added, and only use libraries like System, System.IO, and Microsoft.SqlServer.Dts.Runtime.

Working Script for Folder Creation #

Once you've sorted out the load error, here's a simple, tested C# script that creates a folder only if it doesn't exist. It uses SSIS variables properly and handles errors:

using System;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;

public class ScriptMain : UserComponent
{
    public override void Main()
    {
        // Replace "User::TargetFolderPath" with your actual variable name
        string targetFolderPath = Dts.Variables["User::TargetFolderPath"].Value.ToString();

        try
        {
            // Check if folder exists; create it if not
            if (!Directory.Exists(targetFolderPath))
            {
                Directory.CreateDirectory(targetFolderPath);
                Dts.Events.FireInformation(0, "Folder Creator", $"Created folder: {targetFolderPath}", string.Empty, 0, ref fireAgain);
            }
            else
            {
                Dts.Events.FireInformation(0, "Folder Creator", $"Folder already exists: {targetFolderPath}", string.Empty, 0, ref fireAgain);
            }

            // Mark task as successful
            Dts.TaskResult = (int)DTSExecResult.Success;
        }
        catch (Exception ex)
        {
            // Log error to SSIS and mark task as failed
            Dts.Events.FireError(0, "Folder Creator Error", ex.Message, string.Empty, 0);
            Dts.TaskResult = (int)DTSExecResult.Failure;
        }
    }

    // Needed for FireInformation calls
    bool fireAgain = true;
}

Setup Steps for This Script: #

• In your SSIS package, create a string variable (e.g., User::TargetFolderPath) and set its value to your target folder's full path (like C:\SSIS_Output\MyFiles).
• Open the script task editor, go to the Script tab, and add your variable to the ReadOnlyVariables list (so the script can access it).
• Replace the default script with the code above, making sure the variable name matches what you created.

Final Notes #

After applying these fixes and using the script, run your package again. The load error should be gone, and the folder will be created only when needed. If you still run into issues, double-check that your variable is correctly configured and that the runtime account has access to the target folder location too.

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