SSIS Script Task reference dll programmatically

Question

I have built software that is capable of exporting DTSX package automatically. This package among other objects has also a ScriptTask (C#). All are compiled and run just fine.

Now the new requirement is to call a class in that ScriptTask, which exists inside an external DLL we have built, so other applications can consume the same code. We did our homework, and we included this DLL into the GAC successfully during the installation of the software.

The problem is that "using our library" is still not recognized in the script.

While searching a little bit, we figured out, that we need to reference this DLL also inside the References folder. This we can do it of course via DataTools / VisualStudio UI.

The issue is that we need to do that programmatically:

We have this piece of code that generates the Project

task.ScriptingEngine.VstaHelper.LoadNewProject(task.ProjectTemplatePath, null, "MyScriptProject");

And also, we have this piece of code that creates the MainScript

task.ScriptingEngine.VstaHelper.AddFileToProject(ScriptName + ".cs", MainScript.ToString());

I am unable to figure out how I can include the reference DLL programmatically.

Ockert Ockert · Accepted Answer · 2020-11-12T09:40:10

Updated Answer

You can programmatically update the script task by replacing the appropriate XML node in the the DTSX file

The node path depends on where the script task has been created within the SSIS package, in my case the node path was

/DTS:Executable/DTS:Executables/DTS:Executable/DTS:ObjectData/pipeline/components/component[@refId="Package\Data Flow Task\Script Component"]/properties

The @refId you will be looking for will start with Package \ Dataflow name \ Component name

This node will have sub nodes which contains the C# scripts as well as the binary that was built off this script

The property name "SourceCode" contains the C# scipts in an array called arrayElements, the array will have three sub nodes for each file, these subnodes are called arrayElement, first value is the relative path and name, second is file encoding and third is the file content

The property name "BinaryCode" contains the .dll that was build from the scripts, it also contains an arrayElement array with two entries, first the dll name and the second the base64 encoded dll binary

To get the data to populate these items you will need to create a template of the C# build directory, apply your changes, build the code and take the resulting files and replace them on their appropriate nodes

To create the template open the script via SSIS task,

Click on the project in solution explorer to and go file save VstaProject.sln
Go to the saved folder, you get the folder off the solutions properties dialog
Copy this folder somewhere so that you can reuse it to build your custom stuff
Modify the .cs files in your template directory and add your custom reference dll's to your .csproj file
Call MSBUILD in the same directory as your .csproj to output the dll, its important that you use the VS MSBUILD, VS2017 you can find it in

C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin\msbuild.exe

Take these files and package them into a XML node in the DTSX file

Initial Answer

Microsoft provides a workaround for loading DLL's that aren't in the GAC

Load assembly that isnt in the GAC

Please see below extract from a SSIS script, I loaded the JSON dll's from the nuget install directory. This DLL is not in the GAC

static ScriptMain()
{
    AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
}
static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
    if (args.Name.Contains("Newtonsoft.Json"))
    {
        string path = @"C:\Program Files\Microsoft SDKs\Azure\.NET SDK\v2.9\bin\plugins\Diagnostics\";
        return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "Newtonsoft.Json.dll"));
    }
    return null;
}

SSIS Script Task reference dll programmatically

1 Answers