Set Script Task code dynamically in SSIS 2012

Question

In my application, a script task is created dynamically.

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.");
        }
    }

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

• InitNewScript
• AddProjectReference
• AddCodeFile
• SaveScriptToStorage
• CloseIDE
• Build
• ShowDesigner

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

Titanomachy

+1 and a star for you since I've never tried to do this. The documentation I find refers to the ScriptingEngine and states "This API supports the SQL Server 2012 infrastructure and is not intended to be used directly from your code" so I must not be looking hard enough. Pity that I have production fires to attend, this sounds like a fun challenge – Askew

To push you in the right direction (since I've only done this for Script Components and not Script Tasks) there is a new VSTA interface for SSIS 2012, the VSTAHelper class - tinyurl.com/vstahelper - where all of your methods went. – Argil

@KyleHale i am trying to add reference to script component, can u help? below code not working ScriptComponentHost host = (DesignScriptComponent as IDTSManagedComponent100).InnerObject as ScriptComponentHost; VSTAComponentScriptingEngine _currentScriptingEngine = host.CurrentScriptingEngine; VSTAScriptProjectStorage stor = new VSTAScriptProjectStorage(); _currentScriptingEngine.VstaHelper.SaveProjectToStorage(stor); _currentScriptingEngine.VstaHelper.LoadVSTA2Project(stor, null,new List<string>() { "System.Data.DataSetExtensions.dll" }); – Lillylillywhite

Answer 1

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.

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:

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 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:\temp\package.dtsx", xml);
}

Mallon

Work with Sql Server 2017 – Merrymerryandrew

It works exactly how i wanted. Thanks! +1 – Westphal