Creating a reference to a custom assembly from an SSIS Script Task - vb

Question

I am looking for a way to create an "Imports Newtonsoft.Json.Linq" in my SSIS script task, but this is apparently not as simple as a "manage NuGet Packages" in SSIS. So is there another way around this.

I have found a link to what looks to me like a possible solution, but as I am no skilled programmer, I am having trouble translating from C# into vb. I am hoping that there is someone out there that can help me, or perhaps guide me in another direction.

I am using Visual Studio 2010 Shell, .Net Framework 4.6.0, and the company code language is vb.

Here is a copy of the C# code from the link above. :

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
     static ScriptMain()
     {
         AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
     }
     static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
     {
         if (args.Name.Contains("ssisHelper"))
         {
             string path = @"c:\temp\";
             return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "ssisHelper.dll"));
         }
         return null;
     }
    . . .

Chandan Kumar · Answer 1 · 2018-01-09T12:38:15.580

3

Not Possible.

If you add any third party library(.dll) file, the SSIS will run inside the visual studio(local). So, in local it will work fine (while running in Visual Studio). But once you run it through the SQL Server Job/ETL, it will not.

The only way is you need to add your third party library/libraries in "GACUTIL" of the server where your SSIS is running(through SQL-JOB/ETL...).

As you have mentioned, you are using "Newtonsoft.Json", so, copy the Newtonsoft.Json.dll to the server (gacutil) where SSIS package is running.

Try this and let me know if you face any issues

edited Jan 09 '18 at 12:38

answered Jan 09 '18 at 12:15

Chandan Kumar

4,570
4
42
62

Thank you Chandan, for making me abandon the wrong path. :-) – Jan 11 '18 at 08:16

Hadi · Accepted Answer · 2018-01-09T21:00:21.170

1

You have to add Newtonsoft.Json dll file to the following directories

(1) .Net Framework dll directory

 C:\Windows\Microsoft.NET\Framework\v[.net installed version]\

example

C:\Windows\Microsoft.NET\Framework\v4.0.30319\

(2) SQL server data tools dll runtime directory

C:\Program Files (x86)\Microsoft SQL Server\[SQL Server version]\DTS\Binn

example

C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn

After that, in the Script Task, go to project explorer, you and then add this dll as a reference, it will appear in the references listed.

edited Jan 09 '18 at 21:00

answered Jan 09 '18 at 19:30

Hadi

36,233
13
65
124

1

Thank you - both of you, really! That did it!. I am sorry I cannot mark both of your answers, but as Hadi spelled out for me in capital letters exactly where to put the dll, I choose to mark his answer. – Jan 11 '18 at 08:14
@kiro You can upvote many answers. And just accept one. You are always welcomed. – Hadi Jan 11 '18 at 08:19
1

For (2) above, I found in Data Tools vs2015, I had to add my .dll to either the 'PipelineComponents' or 'Tasks' folders ... 'Binn' didn't work for me. Also, it appears that you only need to add the dll to one of the SQL Server Version folders (in my case, the latest), otherwise there are duplicate references available in the 'Add References' dialog later! – QA Collective May 08 '18 at 05:20
1

@QACollective you're right you have to add the dll to the sql server version related to ssis – Hadi May 08 '18 at 06:47
I am trying to do this, using vs2017 version but can't seem to do it. Posted the question [here](https://stackoverflow.com/q/56037106/1257607) – DanielV May 08 '19 at 09:10

Creating a reference to a custom assembly from an SSIS Script Task - vb

2 Answers2

Linked