1

I have a SSIS package that was originally developed to target SQL Server 2015 that does a variety of things in C# Scripts using SQL Server Management Objects (SMO) that now needs to be upgraded to target SQL Server 2019. Eventually, this package is deployed to the server to run in a SQL Agent Job.

Starting in SQL Server 2017, SMO was moved to a NuGet package and is no longer included in SQL Server Feature Packs as it was for previous versions.

The issue I have is that Integration Services Projects do not really support NuGet. As discussed in the below link:

SSIS Script Task cant find reference to assembly

As mentioned in the above ticket, I believe I could manually install the .dll's to the server if necessary but I am curious if there are other solutions to this issue.

Questions:

  • Is there a way to get Script Tasks in SSIS packages to utilize the SMO NuGet?
  • If "no" to the above, is there another common method of executing C# script via SQL server other than installing the .dll's to the server?
Urith
  • 111
  • 2
  • 2
    SMO is installed with SQL Server, so if you have a 2019 instance of that installed, you can reference the dlls you need from the GAC. Would that work for you? – Mark Wojciechowicz Apr 15 '21 at 20:43
  • There is no SQL Server **2015** version - only 2012, 2014, 2016, 2017 and 2019 - take your pick – marc_s Oct 12 '21 at 05:36

1 Answers1

0

You would be better off installing SMO DLLs into target server Global Assembly Cache. SSIS still does not use local assemblies and you have to introduce some workarounds to defeat that.
You can do a simple MSI installation with SMO DLLs - for example see WIX deploy two assemblies to GAC or What's the 'correct' way of registering/installing an Assembly to the GAC?.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33