在 SSIS 2012 中动态设置脚本任务代码 [英] Set Script Task code dynamically in SSIS 2012

问题描述

在我的应用程序中，脚本任务是动态创建的.

In my application, a script task is created dynamically.

在 SQL Server 2008 的 SSIS 实现中，以下方法运行良好.

In SQL Server 2008's implementation of SSIS, the following method worked fine.

    private void SetSourceCode(ScriptTask scriptTask, string code, string codeName)
    {
        string fileName = "ScriptMain.vb";
        string language = "VisualBasic";
        string proj = ".vbproj";

        scriptTask.ScriptLanguage = VSTAScriptLanguages.GetDisplayName(language);

        scriptTask.ScriptingEngine.InitNewScript(language,
        scriptTask.ScriptProjectName, proj);

        scriptTask.ScriptingEngine.ShowDesigner(false);
        scriptTask.ScriptingEngine.AddCodeFile(fileName, code);

        if (!scriptTask.ScriptingEngine.Build())
            throw new Exception("Failed to build vb script code: " + codeName);
        scriptTask.ScriptingEngine.SaveScriptToStorage();
        if (!scriptTask.ScriptingEngine.CloseIDE(false))
        {
            throw new Exception("Unable to close Scripting engine.");
        }
    }

如何将此代码迁移到 SQL Server 2012，因为以下方法已从 SQL Server 2012 dll-s(程序集)中删除:

How do I migrate this code to SQL Server 2012, because following methods are removed from SQL Server 2012 dll-s (assemblies):

• InitNewScript
• 添加项目参考
• 添加代码文件
• SaveScriptToStorage
• 关闭IDE
• 构建
• ShowDesigner

一般情况下，如何在 SQL Server 2012 中动态设置脚本任务的源代码?

Generally, how do I dynamically set source code for script task in SQL Server 2012?

推荐答案

正如您所注意到的，您可以在 2008 年使用的 VSTA 辅助方法已在 2012 年移动/删除.仍然可以这样做，但代码已更改.

As you've noticed, the VSTA helper methods you could use in 2008 were moved/removed in 2012. It is still possible to do, but the code has changed.

最简单的方法是使用 VstaHelper.LoadProjectFromFolder().

The easiest thing to do is load an existing project using VstaHelper.LoadProjectFromFolder().

如果您想动态添加脚本文件，请参阅下面的代码段.您需要记住两个主要事项:

If you want to dynamically add script files, see the snippet below. There are two main things you need to keep in mind:

ScriptingEngine 和 VstaHelper 类代表 VSTA 本身.这是您创建项目和添加新文件的地方.您不能在此处直接删除或替换现有文件.当您调用 SaveProjecToStorage() 时，就像关闭 VSTA 窗口一样……它将项目和编译后的二进制文件保存到 ScriptTask.

The ScriptingEngine and VstaHelper classes represent VSTA itself. This is where you’d create the project, and add new files. You cannot remove or replace an existing file directly here. When you call SaveProjecToStorage(), it's like closing the VSTA window … it saves the project and compiled binary to the ScriptTask.

ScriptTask.ScriptStorage 允许您直接操作源文件内容.从这里，您可以修改文件的内容.

ScriptTask.ScriptStorage allows you to directly manipulate the source file contents. From here, you can modify the content of a file.

以下代码片段应该可以帮助您入门.

The following code snippet should help you get started.

static void Main(string[] args)
{
    // 1. Create new package, and add a script task
    var pkg = new Package();
    var exec = pkg.Executables.Add("STOCK:ScriptTask");
    var th = (TaskHost)exec;
    th.Name = "Script Task";
    th.Description = "This is a Script Task";
    var task = (ScriptTask)th.InnerObject;

    // 2. Set the script language - "CSharp" or "VisualBasic"
    task.ScriptLanguage = VSTAScriptLanguages.GetDisplayName("CSharp");

    // 3. Set any variables used by the script
    //task.ReadWriteVariables = "User::Var1, User::Var2";

    // 4. Create a new project from the template located in the default path
    task.ScriptingEngine.VstaHelper.LoadNewProject(task.ProjectTemplatePath, null, "MyScriptProject");

    // 5. Initialize the designer project, add a new code file, and build
    //task.ScriptingEngine.VstaHelper.Initalize("", true);
    //task.ScriptingEngine.VstaHelper.AddFileToProject("XX.cs", "FileContents");
    //task.ScriptingEngine.VstaHelper.Build("");

    // 6. Persist the VSTA project + binary to the task
    if (!task.ScriptingEngine.SaveProjectToStorage())
    {
        throw new Exception("Save failed");
    }

    // 7. Use the following code to replace the ScriptMain contents
    var contents = File.ReadAllText("path to file");
    var scriptFile =
        task.ScriptStorage.ScriptFiles["ScriptMain.cs"] =
        new VSTAScriptProjectStorage.VSTAScriptFile(VSTAScriptProjectStorage.Encoding.UTF8, contents);


    // 8. Reload the script project, build and save
    task.ScriptingEngine.LoadProjectFromStorage();
    task.ScriptingEngine.VstaHelper.Build("");

    // 9. Persist the VSTA project + binary to the task
    if (!task.ScriptingEngine.SaveProjectToStorage())
    {
        throw new Exception("Save failed");
    }

    // 10. Cleanup
    task.ScriptingEngine.DisposeVstaHelper();

    // 11. Save
    string xml;
    pkg.SaveToXML(out xml, null);

    File.WriteAllText(@"c:	emppackage.dtsx", xml);
}

这篇关于在 SSIS 2012 中动态设置脚本任务代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！