How to avoid manually browsing DLL in Add Reference of Script Task when deploying package on production?

Question

I use EPPlus.dll library for generating Excel files on the fly for attachment and generate mailer in Script Task of SSIS package.

When there is new requirement for change in mailer comes, I do the change in Script Task on my local machine and send the built package file ( .dtsx ) to DBA team for deployment.

Now everytime I have to ask the DBA team to share production server screen with me where I:

1. Open the file in Visual Studio Data Tools solution
2. Browse to the dll location
3. Add Reference to the dll in Script Task.

Then they import the package file in MSDB from where scheduled job references and executes the package.

If I dont do the above step, the Script Task throws error of reference not found.

Error 1 The type or namespace name 'OfficeOpenXml' could not be found (are you missing a using directive or an assembly reference?)

I overcame challenge of installing DLL inside GAC that is being referenced in Script Task by dynamically loading the assembly as below'

public void Main()
{
     AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);

}
  private System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
  {
            return System.Reflection.Assembly.LoadFrom(System.IO.Path.Combine(strDLLPath, "EPPlus.dll"));
   }

But I am, not able to find steps to avoid manually browsing and adding DLL reference. Please help as DBA team is reluctant/ avoids sharing screen.

Alternatively, what is the correct/best practice way to get package file deployed on server in which external dll is used if I dont have direct access to production server.

Answer 1

I don't think there is a direct way to do that from integration services packages, because the only workaround you can do it is - (what you have done using CurrentDomain_AssemblyResolve function) - loading an assembly from a location instead of installing it into GAC.

There are 3 ways for using custom dll's on deployment:

• Assigning DLL's to the GAC
• Using the AssemblyResolve Function
• Copy all Dll's to the sql server DTS Assemblies folder (example for SQL Server 2008: C:\Program Files\Microsoft SQL Server\100\DTS\Binn) and to the .Net framework assemblies folder.

if the problem is to ask for screen sharing, you can create a small installation wizard that copy these dlls to the specific location and ask the dba team to execute it.

Workaround

When searching for this issue, i found an interesting workaround by using a Web Service instead of direct dll, so you can create a web service which contains the methods you are using and add a Web reference instead of a Local assembly

Side Note: i didn't try this approach before, but i am only trying to help

Useful Links & References

• SSIS custom DLLs during deployment
• How to load an Assembly in a SSIS script task that isn’t in the GAC
• SQL Server SSIS Custom DLL Folders
• Using Custom DLL's in an SSIS Script Task
• SSIS Script Task cant find reference to assembly
• Referencing Other Assemblies in Scripting Solutions
• Access WebService via SSIS Script Component
• Calling a secure webservice in SSIS through script task
• Consume Webservice via SSIS Script Component
• HOW TO: Write a Simple Web Service by Using Visual C# .NET